Macro to change colors of specific characters in a range

Question

I wanted to develop a macro for my worksheet that would pick up a range and change the color of specific chars based on its color. This was similarly answered before here and I took Gary's Student code as the basis for my script. It worked, but I have noticed a little problem that I couldn't solve.

When I have a cell that already starts with a value with a different color than the pattern, it only changes the first character to the color I want, and changes the rest of the chars and words next to it to the pattern. It only happens in this case, for others cells starting in black (pattern) it works perfectly.

Ex.: Suppose that the italic characters are red (color that I want to change), bold characters are blue (color to substitute red) and the rest is the automatic color (black):

Cell value before applying macro: This is a value. Cell value after applying macro: This is a value.

I have searched for it and got no success.
Could it be an Excel bug or an error in the script?

See my version of the code below:

Sub ColorChange()
   Dim I As Long, J As Long, K As Long 'I = Rows, K = Columns, J = Chars
   For K = 6 To 8 'For columns F to H
       For I = 2 To 200 'For rows 2 to 200
           For J = 1 To Len(Cells(I, K).Value)
               If Cells(I, K).Characters(Start:=J, Length:=1).Font.Color <> vbAutomatic Then
                   Cells(I, K).Characters(Start:=J, Length:=1).Font.Color = RGB(226, 107, 10)
                   Cells(I, K).Characters(Start:=J, Length:=1).Font.Bold = True
               End If
           Next J
       Next I
   Next K
End Sub

Answer 1

Firstly there is no such colour index (well… color index) as vbAutomatic; rather, you would need to use xlAutomatic (-4105) to represent the Automatic colour option.

Secondly, the Color property of Font will return an RGB value representing your chosen colour (0=black, 2²⁴ – 1=white) instead of the index of the option you chose. I surmise that you're after the ColorIndex property, although checking for Color=0 would probably be wiser: the Black font colour option doesn't have Index -4105, but Index 1.

Thence, the updated J loop:

       For J = 1 To Len(Cells(I, K).Value)
           If Cells(I, K).Characters(Start:=J, Length:=1).Font.Color <> 0 Then
               Cells(I, K).Characters(Start:=J, Length:=1).Font.Color = RGB(226, 107, 10)
               Cells(I, K).Characters(Start:=J, Length:=1).Font.Bold = True
           End If
       Next J

Answer 2

I had the same issue when I had formulas in the cells. After I copied/pasted them as values, I had no problems with my macro.