Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hightlight Rows Problem (Excel 2000)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hightlight Rows Problem (Excel 2000)

    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

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hightlight Rows Problem (Excel 2000)

    Yes, Hans, it works perfectly.

    Thanks once again.

  4. #4
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hightlight Rows Problem (Excel 2000)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •