Need Help: Dynamic Range

D

Donnie Stone

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
 
K

keepITcool

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 :
 
A

Andoni

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

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