VBA Highlighting Row if Column A contains "Total"

[sbrandwe]

I am trying to highlight an entire row if the cell in Column A contains the text Total starting from row 5.

Any help anyone can give me, I would greatly appreciate.

Here is my current code:


Dim rng As Range
Dim c As Range
Dim endrow As Long
Dim endcol As Long


endrow = Range("A" & Rows.Count).End(xlUp).Row
endcol = Range("IV3").End(xlToLeft).Column


Set rng = Range(Cells(4, 1), Cells(endrow, endcol))


For i = 4 To endrow
If Range("A" & i).Value = "*Total*" Then
Rows(i).Select
Selection.Interior.Color = RGB(192, 192, 192)

End If

Next i

 

[thes4s67]

something like this?

Sub HighlightRow

endrow = Range("A" & Rows.Count).End(xlUp).Row

For each cell in range("A5:A" & endrow)
  if cell.value = "Total" Then
     cell.entirerow.interior.colorindex = 5
  end if
Next

End Sub

 

[sbrandwe]

That is not working either. Any other suggestions?

 

[thes4s67]

Hm. it works for me

 

[Rick Rothstein]

I am trying to highlight an entire row if the cell in Column A contains the text Total starting from row 5.

Any help anyone can give me, I would greatly appreciate.

Here is my current code:


Dim rng As Range
Dim c As Range
Dim endrow As Long
Dim endcol As Long


endrow = Range("A" & Rows.Count).End(xlUp).Row
endcol = Range("IV3").End(xlToLeft).Column


Set rng = Range(Cells(4, 1), Cells(endrow, endcol))


For i = 4 To endrow
If Range("A" & i).Value Like "*Total*" Then
Rows(i).Select
Selection.Interior.Color = RGB(192, 192, 192)

End If

Next i

For your posted code, you need to change the equal sign (which only looks for exact text strings, no wildcards) to the Like operator as shown in red above. If you have a huge amount of rows to process, the following is a more efficient way to do it (the loop only iterates the cells with the word total in them no matter how many other cells there are in the column; so if there are only three cells with the word total in them, then my code performs exactly three loops and finishes even if there were 100,000 cells in the column)...

Sub HighlightTotals()
  Dim Cell As Range, Addr As String
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    Set Cell = .Find("*total*", , xlValues, , , , False, , False)
    If Not Cell Is Nothing Then
      Addr = Cell.Address
      Do
        Cell.EntireRow.Interior.Color = RGB(192, 192, 192)
        Set Cell = .FindNext(Cell)
      Loop While Not Cell Is Nothing And Cell.Address <> Addr
    End If
  End With
End Sub

 

[effendrew]

For your posted code, you need to change the equal sign (which only looks for exact text strings, no wildcards) to the Like operator as shown in red above. If you have a huge amount of rows to process, the following is a more efficient way to do it (the loop only iterates the cells with the word total in them no matter how many other cells there are in the column; so if there are only three cells with the word total in them, then my code performs exactly three loops and finishes even if there were 100,000 cells in the column)...

Sub HighlightTotals()
  Dim Cell As Range, Addr As String
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    Set Cell = .Find("*total*", , xlValues, , , , False, , False)
    If Not Cell Is Nothing Then
      Addr = Cell.Address
      Do
        Cell.EntireRow.Interior.Color = RGB(192, 192, 192)
        Set Cell = .FindNext(Cell)
      Loop While Not Cell Is Nothing And Cell.Address <> Addr
    End If
  End With
End Sub

how would you change the highlight from ENTIRE ROW to just CELL A thru F on that row?

 

[Rick Rothstein]

If I understand your question correctly, change this line of code...

Cell.EntireRow.Interior.Color = RGB(192, 192, 192)

to this...

Cell.Resize(, 6).Interior.Color = RGB(192, 192, 192)

 

[DanteAmor]

Here are a couple of options to consider.

If there are few records:

Sub Macro1()
  ActiveSheet.Range("A1:F" & Range("A" & Rows.Count).End(3).Row).AutoFilter 1, "=*total*"
  ActiveSheet.AutoFilter.Range.Range("A2:F" & Range("A" & Rows.Count).End(3).Row).Interior.Color = RGB(192, 192, 192)
  ActiveSheet.ShowAllData
End Sub

If they are more than 100,000

Sub Macro2()
  Dim a As Variant, r As Range, lr As Long, i As Long
  lr = Range("A" & Rows.Count).End(3).Row
  Set r = Range("A" & lr + 1)
  a = Range("A1:A" & lr).Value2
  For i = 1 To UBound(a)
    If LCase(a(i, 1)) Like "*total*" Then Set r = Union(r, Range("A" & i, Range("F" & i)))
  Next
  r.Interior.Color = RGB(192, 192, 192)
  Range("A" & lr + 1).Interior.Color = xlNone
End Sub

 

[effendrew]

If I understand your question correctly, change this line of code...

Cell.EntireRow.Interior.Color = RGB(192, 192, 192)

to this...

Cell.Resize(, 6).Interior.Color = RGB(192, 192, 192)

thanks Rick. clean and simple.

 

[Ivy_1011]

If I understand your question correctly, change this line of code...

Cell.EntireRow.Interior.Color = RGB(192, 192, 192)

to this...

Cell.Resize(, 6).Interior.Color = RGB(192, 192, 192)

Hi Rick,

This worked when I tried it. How can I modify is so that I do not have to run the macro. Also, if I has different words that need to be color coded, for example "total", "qty", "sum" and I want a different color for each row highlighted. I change it to Worksheet_Chage so that it can work without running a macro button; but how can I add 2 more words to high light and two other other colors?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range, Addr As String

With Range("A1", Cells(Rows.count, "A").End(xlUp))
Set Cell = .Find("*total*", , xlValues, , , , False, , False)
'Set Cell = .Find("*S*", , xlValues, , , , False, , False)



If Not Cell Is Nothing Then
Addr = Cell.Address
Do
'Cell.EntireRow.Interior.Color = RGB(192, 192, 192)
Cell.Resize(, 8).Interior.Color = RGB(192, 192, 192)
'Cell.Resize(, 8).Interior.Color = RGB(192, 190, 191)
Set Cell = .FindNext(Cell)



Loop While Not Cell Is Nothing And Cell.Address <> Addr





End If
End With
End Sub