Hightlight Rows Problem (Excel 2000)

Topic

New Reply

I currently use the following macro to highlight alternating rows based on the cell that is selected when I run the macro:

Sub HighlightAltRows()

ActiveCell.Select

Do
If IsEmpty(ActiveCell.Value) Then
Range(“A1”).Select
Exit Sub
Else
Range(ActiveCell.End(xlToRight), ActiveCell).Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
ActiveCell.Offset(2, 0).Select
End If
Loop
End Sub

However, if a row contains columns without data, then the higlighting for that rows ceases and leaves the remainder of the row unhighlighted. How can I modify this macro to determine how many columns with data are present in the row of the cell selected and then use this to make the highlighted rows the same length? (basically the row from the cell selected will have data in all of the columns) I want the highlighting to be this number of rows wide until the macro encounters a row that is blank.

Thanks in advance for any assistance.

Reply | Quote

Replies

Does this do what you want?

Sub HighlightAltRows()
Dim lngCols As Long
Dim lngRowOffset As Long
Dim rng As Range
If IsEmpty(ActiveCell) Then
Range(“A1”).Select
Exit Sub
End If
Set rng = Range(ActiveCell, ActiveCell.End(xlToRight))
lngCols = rng.Columns.Count
Do
With rng.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Set rng = rng.Offset(2, 0)
Loop Until Not FilledRange(rng)
End Sub

Function FilledRange(rng As Range) As Boolean
Dim ocell As Range
For Each ocell In rng.Cells
If Not IsEmpty(ocell) Then
FilledRange = True
Exit Function
End If
Next ocell
End Function

Reply | Quote

Yes, Hans, it works perfectly.

Thanks once again.

Reply | Quote

(Edited by HansV to make URL clickable – see Help 19)

For what it’s worth, the excellent ASAP Utilities add-in from http://www.asap-utilities.com[/url%5D will do this (and much else!) automagically.
Could there also be a solution to your challenge based on conditional formatting?

Reply | Quote