Color Code Cells VBA Macro

[richmcgill]

I heard that VBA / Macro could help me with color coding a range of cells based on adding a character to another cell.

In this case I am trying to clear an amount from a balance sheet.
If I add a C to clear the item I need the three cells to the left of the amount to color code yellow.
Can this be done and what would this macro look like?


[TABLE="width: 327"]
<colgroup><col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2446;"> <col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3072;"> <col width="103" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3669;"> <col width="62" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2218;"> <col width="57" style="width: 43pt; mso-width-source: userset; mso-width-alt: 2019;" span="2"> <tbody>[TR]
[TD="width: 69, bgcolor: transparent"]Date[/TD]
[TD="width: 86, bgcolor: transparent"]Loan Number[/TD]
[TD="width: 103, bgcolor: transparent"]Customer Name[/TD]
[TD="width: 62, bgcolor: transparent"] Amount [/TD]
[TD="width: 57, bgcolor: transparent"] [/TD]
[TD="width: 57, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]5/19/2019[/TD]
[TD="bgcolor: yellow, align: right"]12345[/TD]
[TD="bgcolor: yellow"]Smith[/TD]
[TD="bgcolor: transparent"] $744.93
[/TD]
[TD="bgcolor: transparent"] C
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5/19/2019[/TD]
[TD="bgcolor: transparent, align: right"]3456[/TD]
[TD="bgcolor: transparent"]Jones[/TD]
[TD="bgcolor: transparent"] $ 784.89 [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5/19/2019[/TD]
[TD="bgcolor: transparent, align: right"]6789[/TD]
[TD="bgcolor: transparent"]Johnson[/TD]
[TD="bgcolor: transparent"] $ 333.43 [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[lrobbo314]

As much as I like using VBA, you don't really need to here. A simple conditional formatting rule will do the trick.

Select the entire range of values in columns A-C, add the conditional formatting formula below, and add the yellow background format.

=$E2="C"

 

[lrobbo314]

But, if you really want VBA, here is one way to do it.

Sub HighC()
Dim r As Range: Set r = Range("E2:E" & Range("E" & Rows.Count).End(xlUp).Row)
Dim cel As Range

For Each cel In r
    If cel.Value = "C" Then
        Range("A" & cel.Row & ":C" & cel.Row).Interior.ColorIndex = 6
    End If
Next cel

End Sub

 

[Rick Rothstein]

But, if you really want VBA, here is one way to do it.

Sub HighC()
Dim r As Range: Set r = Range("E2:E" & Range("E" & Rows.Count).End(xlUp).Row)
Dim cel As Range

For Each cel In r
    If cel.Value = "C" Then
        Range("A" & cel.Row & ":C" & cel.Row).Interior.ColorIndex = 6
    End If
Next cel

End Sub

Another way to write this macro...

Sub HighC2()
  Intersect([A:C], [E:E].SpecialCells(xlConstants).EntireRow).Interior.Color = vbYellow
End Sub

 

[richmcgill]

Thank you very much for the help.

I really do appreciate everyone taking the time to help.

 

[richmcgill]

Thank you very much for the help.

I really do appreciate everyone taking the time to help.