Need Help: Dynamic Range
I need help changing the: For Each rng In Range("B2:B5000") to a dynamic
statement that looks for the last column/row used in the sheet.
Thanks in advance for the help.
Sub Green_Bar()
Dim rng As Range
Dim OldVal As Variant
Dim Gray As Boolean
Gray = True
OldVal = Range("B2").Value
For Each rng In Range("B2:B5000") ' WANT TO BE ABLE TO MAKE THIS DYNAMIC
If rng.Value = OldVal Then
If Gray Then
rng.EntireRow.Interior.ColorIndex = 2
Else
rng.EntireRow.Interior.ColorIndex = 4
End If
Else
OldVal = rng.Value
Gray = Not Gray
If Gray Then
rng.EntireRow.Interior.ColorIndex = 2
Else
rng.EntireRow.Interior.ColorIndex = 4
End If
End If
Next
End Sub
|