VBA to highlight row based on a cell within row

[Guitarmageddon]

Hello all. Many thanks to @Fluff for getting me sorted in some other steps within this current macro.

I would like to add some VBA in at the end of my macro that does two different sets of highlights:
1.) highlight the entire row if the site number in Column M equals 60001 OR 70001. I want this DARK purple
2.) Highlight the entire row if the site number in column M equals 16001. I want this LIGHT purple.
I have given a quick example of some of the highlights below.

Notes:
My report length is dynamic, but you can reference column A since that will always go down to the end of the report
My number of columns extends out to AC, which I believe is 29 columns total on the report. If possible Id like to confine the highlight to that.

I had something similar in another macro I had (based on some unrelated values), but it was really hard for me to dissect and repurpose since I cant remember what I did! Id appreciate any help greatly here from the masters!

   Range("A2").Select
    Columns("l:l").ColumnWidth = 10.14
    Do While ActiveCell.Offset(0, 12) <> ""
       If ActiveCell.Offset(0, 12).FormulaR1C1 <> "000" Then
           Range(ActiveCell, ActiveCell.Offset(0, 42)).Select
               With Selection.Interior
                   .Pattern = xlSolid
                   .PatternColorIndex = xlAutomatic
                   .Color = 65535 'yellow
                   .TintAndShade = 0
                   .PatternTintAndShade = 0
               End With
       End If
       ActiveCell.Offset(1, 0).Select
       
    Loop

 

[nemmi69]

Try

Sub HighlightRows()
'1.) highlight the entire row if the site number in Column M equals 60001 OR 70001. I want this DARK purple
' 2.) Highlight the entire row if the site number in column M equals 16001. I want this LIGHT purple.

Dim lRow As Long
Dim RowLoop As Long


lRow = Cells(Rows.Count, "M").End(xlUp).Row

For RowLoop = 1 To lRow
    If Range("M" & RowLoop).Value = 60001 Then
        'dark purple
         Range("M" & RowLoop).Interior.Color = 10498160
    End If
    If Range("M" & RowLoop).Value = 70001 Then
        'light purple
        Range("M" & RowLoop).Interior.Color = 16737996
    End If
Next RowLoop

End Sub

 

[nemmi69]

Sorry you wanted the entire row

Sub HighlightRows()
'1.) highlight the entire row if the site number in Column M equals 60001 OR 70001. I want this DARK purple
' 2.) Highlight the entire row if the site number in column M equals 16001. I want this LIGHT purple.

Dim lRow As Long
Dim RowLoop As Long


lRow = Cells(Rows.Count, "M").End(xlUp).Row

For RowLoop = 1 To lRow
    If Range("M" & RowLoop).Value = 60001 Then
        'dark purple
         Rows(RowLoop & ":" & RowLoop).Interior.Color = 10498160
    End If
    If Range("M" & RowLoop).Value = 70001 Then
        'light purple
        Rows(RowLoop & ":" & RowLoop).Interior.Color = 16737996
    End If
Next RowLoop

End Sub

 

[Guitarmageddon]

So that applies the highlight specifically to the RDC site cell, rather than the whole row. Can you make it the whole row? And one other change too: to "dark purple" rows should apply to the site number 60001 AND 70001. And the light purple should apply to site number 16001 only.

the output ended up looking like this

 

[nemmi69]

2nd code I posted does the whole row

 

[Tom Baron]

Included a loop to kill previous formatting. This is just the way I would have done it:

Dim targetc As Range, vari As Variant, x As Integer
Set targetc = Range(Range("a9"), Range("a100000").End(xlUp))

'clear colour formatting
For Each vari In targetc
    cells.EntireRow.Interior.ColorIndex = 0
Next vari
x = 1
Do While x <= targetc.Rows.Count

If targetc.cells(x, 13) = 60001 Or targetc.cells(x, 13) = 70001 Then
Range(targetc.cells(x, 1), targetc.cells(x, 28)).Interior.Color = 10498160
ElseIf targetc.cells(x, 13) = 16001 Then
Range(targetc.cells(x, 1), targetc.cells(x, 28)).Interior.Color = 16737996
End If
x = x + 1
Loop

 

[Guitarmageddon]

Included a loop to kill previous formatting. This is just the way I would have done it:

Dim targetc As Range, vari As Variant, x As Integer
Set targetc = Range(Range("a9"), Range("a100000").End(xlUp))

'clear colour formatting
For Each vari In targetc
    cells.EntireRow.Interior.ColorIndex = 0
Next vari
x = 1
Do While x <= targetc.Rows.Count

If targetc.cells(x, 13) = 60001 Or targetc.cells(x, 13) = 70001 Then
Range(targetc.cells(x, 1), targetc.cells(x, 28)).Interior.Color = 10498160
ElseIf targetc.cells(x, 13) = 16001 Then
Range(targetc.cells(x, 1), targetc.cells(x, 28)).Interior.Color = 16737996
End If
x = x + 1
Loop

So this works, but I couldnt figure out how to take out the part that removes the color formatting from the existing report. I didnt want it to remove all that. I just wanted the row highlight part you have. I tried to remove myself but it bugged out.

 

[Guitarmageddon]

Sorry you wanted the entire row

Sub HighlightRows()
'1.) highlight the entire row if the site number in Column M equals 60001 OR 70001. I want this DARK purple
' 2.) Highlight the entire row if the site number in column M equals 16001. I want this LIGHT purple.

Dim lRow As Long
Dim RowLoop As Long


lRow = Cells(Rows.Count, "M").End(xlUp).Row

For RowLoop = 1 To lRow
    If Range("M" & RowLoop).Value = 60001 Then
        'dark purple
         Rows(RowLoop & ":" & RowLoop).Interior.Color = 10498160
    End If
    If Range("M" & RowLoop).Value = 70001 Then
        'light purple
        Rows(RowLoop & ":" & RowLoop).Interior.Color = 16737996
    End If
Next RowLoop

End Sub

I responded too quickly, didnt see your reply. Yes this works. Although how would I make the first dark purple part for both sites 60001 AND 70001, and the light purple for site number 16001? Other than that, this works. I find it worked a bit faster than @Tom Baron approach

 

[nemmi69]

Sub HighlightRows()
'1.) highlight the entire row if the site number in Column M equals 60001 OR 70001. I want this DARK purple
' 2.) Highlight the entire row if the site number in column M equals 16001. I want this LIGHT purple.

Dim lRow As Long
Dim RowLoop As Long


lRow = Cells(Rows.Count, "M").End(xlUp).Row

For RowLoop = 1 To lRow
    If Range("M" & RowLoop).Value = 60001 Or Range("M" & RowLoop).Value = 70001 Then
        'dark purple
         Rows(RowLoop & ":" & RowLoop).Interior.Color = 10498160
    End If
    If Range("M" & RowLoop).Value = 16001 Then
        'light purple
        Rows(RowLoop & ":" & RowLoop).Interior.Color = 16737996
    End If
Next RowLoop

End Sub

 

[Guitarmageddon]

Sub HighlightRows()
'1.) highlight the entire row if the site number in Column M equals 60001 OR 70001. I want this DARK purple
' 2.) Highlight the entire row if the site number in column M equals 16001. I want this LIGHT purple.

Dim lRow As Long
Dim RowLoop As Long


lRow = Cells(Rows.Count, "M").End(xlUp).Row

For RowLoop = 1 To lRow
    If Range("M" & RowLoop).Value = 60001 Or Range("M" & RowLoop).Value = 70001 Then
        'dark purple
         Rows(RowLoop & ":" & RowLoop).Interior.Color = 10498160
    End If
    If Range("M" & RowLoop).Value = 16001 Then
        'light purple
        Rows(RowLoop & ":" & RowLoop).Interior.Color = 16737996
    End If
Next RowLoop

End Sub

Perfect!