Jon Melone
Board Regular
- Joined
- Mar 10, 2008
- Messages
- 109
Hi,
I need to help to loop through a worksheet while highlighting a range of cells. Once I have my first range defined, I'm unable to figure out how to move the range to the next appropriate row. The next row is not constant, but can be determined by .End(xlDown).End(xlDown).
'code snapshot starts here
Dim totalLoop As Integer 'number of items with a forecast
Dim fcstHighlight As Range 'highlight forecast values
Dim highlightCounter As Byte 'control highlight of forecast row looping
'assign initial values
totalLoop = Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
Set fcstHighlight = Range(Range("C2"), Range("C2").End(xlToRight))
highlightCounter = 1
'run through loop
Do While totalLoop >= highlightCounter
With fcstHighlight.Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
Set fcstHighlight = ??? 'move to next appropriate row
highlightCounter = highlightCounter + 1
Loop
'end of code snapshot
My goal is to move the range down the spreadsheet based on .End(xlDown).End(xlDown) as there are rows with data to pass-over and then blank rows to skip. The first row after the blank rows is the next fcst row.
My attempts have either created a larger range by starting with the original range and including everything between the last row or only moving to the first cell within the targeted range, but not assigning the full range across the columns.
Jon
I need to help to loop through a worksheet while highlighting a range of cells. Once I have my first range defined, I'm unable to figure out how to move the range to the next appropriate row. The next row is not constant, but can be determined by .End(xlDown).End(xlDown).
'code snapshot starts here
Dim totalLoop As Integer 'number of items with a forecast
Dim fcstHighlight As Range 'highlight forecast values
Dim highlightCounter As Byte 'control highlight of forecast row looping
'assign initial values
totalLoop = Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
Set fcstHighlight = Range(Range("C2"), Range("C2").End(xlToRight))
highlightCounter = 1
'run through loop
Do While totalLoop >= highlightCounter
With fcstHighlight.Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
Set fcstHighlight = ??? 'move to next appropriate row
highlightCounter = highlightCounter + 1
Loop
'end of code snapshot
My goal is to move the range down the spreadsheet based on .End(xlDown).End(xlDown) as there are rows with data to pass-over and then blank rows to skip. The first row after the blank rows is the next fcst row.
My attempts have either created a larger range by starting with the original range and including everything between the last row or only moving to the first cell within the targeted range, but not assigning the full range across the columns.
Jon