Macro to Auto Run When Excel Cell Data Changes

[vwallace]

Hi,

I would like the below macro to automatically run when the cell data in "M5" (on the OSAR tab in Excel) changes. "M5" is linked to another tab and when data in that tab is entered, then this cell will change values from zero to actual words. Below is my if statement the deletes 4 rows if the cell data reads "HOTL_MOTL". Can you please help me make this macro auto run when this data is changed in the cell?

Sub sbVBS_To_Delete_EntireRow()
 [vb]
 If Range("M5").Value <> "HOTL_MOTL" And Range("M5") <> "" Then
 Rows(22).EntireRow.Delete
 Rows(22).EntireRow.Delete
 Rows(22).EntireRow.Delete
 Rows(22).EntireRow.Delete
 End If
 [/vb]
 End Sub

Thanks!

[Excel Fox]

Paste this within the worksheet code module

Private Sub Worksheet_Calculate()
    
    If Range("M5").Value <> "HOTL_MOTL" And Range("M5") <> "" Then
        Application.EnableEvents = False
        Range("22:25").EntireRow.Delete
        Application.EnableEvents = False
    End If
    
End Sub

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313859#p313859
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313855#p313855
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313848#p313848
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313843#p313843
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313792#p313792
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313771#p313771
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313767#p313767
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313746#p313746
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313744#p313744
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313741#p313741
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313622#p313622
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313575#p313575
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313573#p313573
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313563#p313563
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313555#p313555
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533
https://www.eileenslounge.com/viewtopic.php?f=39&t=40265&p=313468#p313468
https://www.eileenslounge.com/viewtopic.php?f=42&t=40505&p=313411#p313411
https://www.eileenslounge.com/viewtopic.php?f=32&t=40473&p=313384#p313384
https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313382#p313382
https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313380#p313380
https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313378#p313378
https://www.eileenslounge.com/viewtopic.php?f=32&t=40473&p=313305#p313305
https://www.eileenslounge.com/viewtopic.php?f=44&t=40455&p=313035#p313035
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312889#p312889
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

[vwallace]

That works except for it still deletes the rows if "HOTL_MOTL" is entered into that field...

Thanks for the response!

[Excel Fox]

If that's your only criteria, then why don't you remove the other criteria? Why do you need the check for blank value in M5?

[vwallace]

Hmm.. I think I need help understanding your statement above.. What I want this macro to do (I believe I probably misstated this in the original post), is when values are entered into that field, I want the four rows to be deleted ONLY IF that cells does not read HOTL_MOTL. I guess I could go the other way and insert rows if it reads HOTL_MOTL.. I need some help with that part.

Thanks!

[Excel Fox]

try this in that case

Private Sub Worksheet_Calculate()
    
    Application.EnableEvents = False
    If Range("M5").Value <> "HOTL_MOTL" Then
        Range("22:25").EntireRow.Delete
    Else
        Range("22:25").EntireRow.Insert
    End If
    Application.EnableEvents = False
    
End Sub