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

[tkbaty]

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.

Thanks!

 

[Fluff]

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

 

[alansidman]

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