CODEWITHSUNDEEP
vbaexcel
mattinwpg
mattinwpg

Reputation: 163

Trigger a macro when a cell changes

I know there are tonnes of questions and answers, but I've got a very rudimentary knowledge of programming in VBA and I'm having a frustrating time making things work.

I have a sheet that has 1000 rows of employees, and each row starts with the name of their manager. i.e. column A has the manager names, columns B, C, D, etc have other info.

My current macro looks at the text in cell J1 and compares it with the text in column A. J1 populates from another worksheet using an "=worksheet1!A1" type thing.

Sub hiderows()

BeginRow = 2
EndRow = 1000
ChkCol = 1

For RowCnt = BeginRow To EndRow

If Cells(RowCnt, ChkCol).Value = Cells(1, "J") Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
End If
Next RowCnt
End Sub

What I'm trying to get is for this to run automatically when the value of J1 changes (i.e. from Tim to Jake). I've found some code to do that, but i can't get it to work. 

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Target.Worksheet.Range("$J$1")) Is Nothing Then

        Application.Run _

'your macro here

End If
End Sub

how do I add my macro? do I keep the End Sub in mine so there are two End Subs? My macro is stored in the same excel book in Module1

Any help combining these in to a workable one would be great, I'm pulling my hair out here...

Upvotes: 2

Views: 577

Answers (1)

SierraOscar
SierraOscar

Reputation: 17647

This should do the trick:

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
        For i = 2 To 1000
            Rows(i).EntireRow.Hidden = (Cells(i, 1).Value = Range("J1").Value)
        Next
    Application.EnableEvents = True
End Sub

Upvotes: 1

Related Questions