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 |
Need Help: Dynamic Range
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 |
Need Help: Dynamic Range
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 |
All times are GMT +1. The time now is 05:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com