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?

Date	Loan Number	Customer Name	Amount
5/19/2019	12345	Smith	$744.93	C
5/19/2019	3456	Jones	$ 784.89
5/19/2019	6789	Johnson	$ 333.43

<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> </tbody>

 

[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.