VBA code for find keyword in column E if found, change font color in matching B column cell


New Member
Hi newbie Excel macros and VBA user here...

I need either macro or VBA code to do the following:

find keyword (not case sensitive) in column E which contains various text notes.
if found, change font color in matching B column cell
Then repeat for all cells in column to final row.
Note I have 3 sections of rows in the spread that are separated by blank rows for subtotalling.

Note2: I actually have two keywords ("died" and "d/c") that I need to find then colorcode the B col cell font
different colors. Bold Blue for died. And bold green for d/c.

I think I should be able to do with a loop or If, Elseif type vba code...but can't figure how to write it.

Would so appreciate any help.



MrExcel MVP, Moderator
Hi & welcome to MrExcel.
How about
Sub tkbaty()
    With ActiveSheet
        .Range("A1:E1").AutoFilter 5, "*died*"
        With .AutoFilter.Range.Offset(1).Columns(2).Font
            .Bold = True
            .Color = vbBlue
        End With
        .Range("A1:E1").AutoFilter 5, "*d/c*"
        With .AutoFilter.Range.Offset(1).Columns(2).Font
            .Bold = True
            .Color = vbGreen
        End With
        .AutoFilterMode = False
    End With
End Sub


Well-known Member
Option Explicit
Option Compare Text

Sub tkbaty()
    Dim i As Long, lr As Long
    lr = Range("E" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 1 To lr
        If Range("E" & i) = "died" Then
            Range("B" & i).Font.Color = vbBlue
            Range("B" & i).Font.Bold
        ElseIf Range("E" & i) = "d/c" Then
            Range("B" & i).Font.Color = vbGreen
            Range("B" & i).Font.Bold
        End If
    Next i
    Application.ScreenUpdating = True
    MsgBox "complete"
End Sub

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...