Search code examples
excelvbareferencetarget

How to use Target Range using the cell value in excel

I have this formula below in excel that hide and unhide row. I want to make it a dynamic reference. So instead of using Range "B50" or range "51:68". I would like to set the said reference to a cell value so that I can change them any time without opening VBA.

Formula:

     If Not Intersect(Target, Range("B50")) Is Nothing Then Cancel = True
     If (Not Intersect(Target, Range("B50")) Is Nothing) And (Target.Count = 1) Then
     Set hideRows = Range("51:68")
     hideRows.EntireRow.Hidden = Not hideRows.EntireRow.Hidden
     End If
Solution 
  • If Not Intersect(Target, Me.Range(Sheet1.Range("A1").Value) Is Nothing Then`

    ...where Sheet1 A1 has the cell address you want to use

    If the address is on the same sheet where the event handler is running then:

    If Not Intersect(Target, Me.Range(Me.Range("A1").Value) Is Nothing Then`