With Worksheets("sheet1")
Dim Irow As Long
Irow = .Range("c" & Rows.Count).End(xlUp).row
Dim lcol As Long
lcol = .Cells(3, 4).End(xlToRight).Column
Dim c As Object
For Each c In .Range(.Cells(3, 4), .Cells(Irow, lcol))
If c.Value Like "*Rc*" Then
c.Font.ColorIndex = 3
ElseIf c.Value Like "*Ra*" Then
c.Font.ColorIndex = 4
ElseIf c.Value Like "*Rb*" Then
c.Font.ColorIndex = 5
ElseIf c.Value Like "*Rd*" Then
c.Font.ColorIndex = 6
ElseIf c.Value Like "*Rf*" Then
c.Font.ColorIndex = 7
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("D3:H13")) Is Nothing Then
With Target
If .Text Like "*Rc*" Then
.Font.ColorIndex = 3
ElseIf .Text Like "*Ra*" Then
.Font.ColorIndex = 4
ElseIf .Text Like "*Rb*" Then
.Font.ColorIndex = 5
ElseIf .Text Like "*Rd*" Then
.Font.ColorIndex = 6
ElseIf .Text Like "*Rf*" Then
.Font.ColorIndex = 7
Else
.Font.ColorIndex = xlColorIndexAutomatic
End If
End With
End If
End Sub