Code to highlight used row highlights rows irregularly

[KC E]

Hello,

I'm using Excel 2013 and have code that highlight rows based on when the value in column E (House column) changes and if the Week column (column D) changes within that hub, then it highlights that row, too--alternating between light green and no color.

The problem:
It doesn't highlight rows to the end of the used range consistently, and I can't see why it chooses to highlight rows part way--to differing columns--but never to the end of the used range.

I've used the code successfully in the past, but for this project, I changed the code in the following manner:
1) I changed where you see "D" and/or "E" in the code below. It used to be "E" and/or "F".
2) I've also added the

Set rng = Range("A1:S" & LR)

and the LR variable.
3) I've added the '-----Remove interior color, '-----Clear formatting, '-----Clear Conditional Formatting, and '-----Add green borders sections of code. The '------Color rows based on Columns D and E is the only original code.
4) I need to tell you that there is code that runs before this that:
a) filters a master spreadsheet based on the House column,
b) copies and pastes it to a new workbook
b) opens the Save As dialog for you to save it where you want, and it automatically names it for you.
c) formats the worksheet in the new saved workbook by deleting, rearranging columns and adding a new column, then formatting the worksheet to be printed (zooming to 90%, rows to repeat at top, landscape orientation, etc.)
When this code is done, the finished worksheet then calls to the code below to color the rows.

What I've tried:
I added the '-----Remove interior color, '-----Clear formatting, and '-----Clear Conditional formatting thinking that that would help clear any formatting for Excel to color the rows to the end of the used range.

I did have conditional formatting in columns G, Q, and R, the Status, WXAA, and TR columns, but I thought the '-----Clear Conditional formatting codes would have taken care of that.

I would appreciate any help. I have uploaded the file if you would like to look. The code is in the module under the Forum.xlsm file.

Sub colorLMReport()
    Dim rng As Range
    Dim LR As Long
    Dim r As Long
    Dim colourIt As Boolean
    Dim colour As XlColorIndex
    Dim lastcol As Long

    colourIt = False
    lastcol = Cells(1, Columns.Count).End(xlToLeft).Column '-2 b/c of A&B columns that are hidden
    LR = Cells(Rows.Count, "E").End(xlUp).Row
    Set rng = Range("A1:S" & LR)
    '-----Remove interior color
    With rng.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    '-----Clear formatting
    rng.Select
    Selection.ClearFormats
    With Selection
        .HorizontalAlignment = xlCenter 'after clear formats, recenter
        .VerticalAlignment = xlBottom
        .WrapText = False
    End With
'-----Clear Conditional Formatting
rng.Select
Selection.FormatConditions.Delete
    '-----Add green borders
    With rng.Borders
        .LineStyle = xlContinuous
        .ThemeColor = 10
        .TintAndShade = -0.249946592608417 'Green, Accent 6, Darker 25%
        .Weight = xlThin
    End With
    Range("C1").Select
    '------Color rows based on Columns D and E
    With ActiveSheet
        r = 2  ' First row of data

        Do While .Cells(r, "D").Value <> ""
            '"D" is Week column, and "E" is House column
            If .Cells(r, "D").Value <> .Cells(r - 1, "D").Value Or _
              .Cells(r, "E").Value <> .Cells(r - 1, "E").Value Then
                colourIt = Not colourIt
            End If

            'Determine which colour to use on this row
            If colourIt Then
                colour = RGB(226, 239, 218) 'light green
            Else
                colour = RGB(255, 255, 255) 'no color
            End If
            'Apply the colouring - starting in row r = 2, 2nd row, and column "A" (A & B are hidden but color them anyway)
          .Range(.Cells(r, "A"), .Cells(r, .Cells(r, lastcol).End(xlToLeft).Column)).Interior.Color = colour
            'Point to the next row of data
            r = r + 1
        Loop
    End With
   
     
End Sub

 

[Nebu]

Hi:

Is this what you are looking for?

Thanks

 

[NARAYANK991]

Hi ,

Another option is to replace your statement with :

rng.Rows(r).Interior.Color = colour

Narayan

 

[KC E]

Hi ,

Another option is to replace your statement with :

rng.Rows(r).Interior.Color = colour

Narayan

Thank you very much, Narayan. This was the line that worked! On the other line above, I received a 'Run Time Error 438: Object does not support this property or Method.'

Also I removed the rng.Select lines that you removed under '-----Clear Formatting and '-----Clear Conditional Formatting.

Here is the final code with the changes Narayan made if it will help anyone. Thank you, again, Narayan.

Sub colorLMReport()

    Dim rng As Range
    Dim LR As Long
    Dim r As Long
    Dim colourIt As Boolean
    Dim colour As XlColorIndex
    Dim lastcol As Long

    colourIt = False
    lastcol = Cells(1, Columns.Count).End(xlToLeft).Column '-2 b/c of A&B columns that are hidden
    LR = Cells(Rows.Count, "E").End(xlUp).Row
    Set rng = Range("A1:S" & LR)
    '-----Remove interior color
    With rng.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    '-----Clear formatting
    rng.ClearFormats
    With rng
        .HorizontalAlignment = xlCenter 'after clear formats, recenter
        .VerticalAlignment = xlBottom
        .WrapText = False
    End With
    '-----Clear Conditional Formatting
    rng.FormatConditions.Delete
    '-----Add green borders
    With rng.Borders
        .LineStyle = xlContinuous
        .ThemeColor = 10
        .TintAndShade = -0.249946592608417 'Green, Accent 6, Darker 25%
        .Weight = xlThin
    End With
  
    '------Color rows based on Columns D and E
    With ActiveSheet
        r = 2  ' First row of data
        Do While .Cells(r, "D").Value <> ""
            '"D" is Week column, and "E" is House column
            If .Cells(r, "D").Value <> .Cells(r - 1, "D").Value Or _
              .Cells(r, "E").Value <> .Cells(r - 1, "E").Value Then
                colourIt = Not colourIt
            End If

            'Determine which colour to use on this row
            If colourIt Then
                colour = RGB(226, 239, 218) 'light green
            Else
                colour = RGB(255, 255, 255) 'no color
            End If
            'Apply the colouring - starting in row r = 2, 2nd row, and column "A" (A & B are hidden but color them anyway)
          rng.Rows(r).Interior.Color = colour
            'Point to the next row of data
            r = r + 1
        Loop
    End With
        
Call ColorForAddToNodes
  
End Sub

(credit: this code was originally provided last yr. by YowE3K from StackOverflow)