Color Coding Entire Rows Based On Cell Data

pdreher13 · Apr 27th 2016

Good morning!

I have started VBA to color code entire rows on a table based on data in specific cells. I am wondering if it is possible to do this but to search for non-exact matches. For example, I am looking for the phrase "HCR CR" in a column titled "Notes". The notes are free form however and can vary widely.

Thank you!

**alansidman** · Apr 27th 2016

Re: Color Coding Entire Rows Based On Cell Data

try this code which assumes "Notes" is in column A. Change as needed.

Code

Option Explicit




Sub HCR()
    Dim c As Range
    Dim rng As Range
    Dim lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Set rng = Range("A1:A" & lr)
    For Each c In rng
        If InStr(c, "HCR CR") > 0 Then
            c.EntireRow.Font.ColorIndex = 3
        End If
    Next c
End Sub

Display More

pdreher13 · Apr 27th 2016

Re: Color Coding Entire Rows Based On Cell Data

Thank you very much Alan! Works perfectly!

Color Coding Entire Rows Based On Cell Data

Participate now!

Share