How to make a macro run each time a specific cell value changes.

[ARC32]

I'm very new to vba so I'm probably making hard work of this, but I've tried to write a macro that will hide certain rows based on the value of other cells. Good news is that when I play the macro it appears to work.

Bad news is that I want to make the macro run every time the value of Cells(180,2), Cells(5,2) or Cells(27,3) changes (regardless of whether it's changed manually by the user, by formula or by another macro).

I've found similar threads elsewhere on here, but like I said, been using vba for a couple of days and afraid I just haven't followed them.

Can anyone help? - My code is as follows:

Sub mcrHideRows()

Dim intNumRows As Integer
Dim intRowCount As Integer

Application.ScreenUpdating = False

intNumRows = 221

If Cells(180, 2) <> "Used" Then Rows(180).Hidden = True
If Cells(180, 2) <> "Used" Then Rows(181).Hidden = True

If Cells(180, 2) = "Used" Then Rows(180).Hidden = False
If Cells(180, 2) = "Used" Then Rows(181).Hidden = False

For intRowCount = 1 To intNumRows

If Cells(5, 2) <> "Other" And Cells(introwcount, 16).Value = "x" Then Rows(introwcount).Hidden = True

If Cells(5, 2) = "Other" And Cells(27, 3) = "Limited" And Cells (introwcount, 14).Value = "x" Then Rows(introwcount).Hidden = True

If Cells(5, 2) = "Other" And Cells(27, 3) = "Non-Limited" And Cells(introwcount, 15).Value = "x" Then Rows(introwcount).Hidden = True

If Cells(5, 2) <> "Other" And Cells(introwcount, 16).Value <> "x" Then Rows(introwcount).Hidden = False

If Cells(5, 2) = "Other" And Cells(27, 3) = "Limited" And Cells(introwcount, 14).Value <> "x" Then Rows(introwcount).Hidden = False

If Cells(5, 2) = "Other" And Cells(27, 3) = "Non-Limited" And Cells(introwcount, 15).Value <> "x" Then Rows(introwcount).Hidden = False

Next introwcount

End Sub

 

[Yabiman]

Well, I've gotten the macro to trigger using the Private Sub Worksheet_Change(ByVal Target As Range) routine, but I have come to the conclusion that my problem is that Autofit is not working over a range of rows. That is a problem for another thread so I will leave this one with thanks to Atroexcell, whose suggestion "Activeworkbook.Sheets("yoursheetnamehere").Rows("15:43").AutoFit" worked at least for one line at a a time.

 

[mikerickson]

Try this syntax

ThisWorkbook.Sheets("Sheet1").Rows("15:23").Rows.AutoFit