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

Donnie, try:


Dim rngData as Range
Set rngData = Range("B2", Range("B65536").End(xlUp))
For Each rng In rngData
....


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Donnie Stone wrote :


For Each rng In Range("B2:B5000") ' WANT TO BE ABLE TO MAKE THIS
DYNAMIC

this will take the range comprised in B2 and the last non empty cell i
column B:

Range(Range("B2"),Range("B65536").end(xlup)

--
Message posted from http://www.ExcelForum.com