Changing colours of certain letters in all words

Occasional Visitor

I made this table and have painted some of the letters orange, but now I want to change the colour to blue in all of the words and can't really seem to find the solution without doing it one by one.

Tried the replace function but it would only replace whole words.Bez názvu.png

Please help!

1 Reply


Here is a macro. Change OldColor to the color you now use, and NewColor to the desired color. You can see the values required if you select Font Color > More Colors... and activate the Custom tab.

Sub ChangeColor()
    Dim OldColor As Long
    Dim NewColor As Long
    Dim rng As Range
    Dim i As Long
    Application.ScreenUpdating = False
    OldColor = RGB(255, 192, 0)
    NewColor = RGB(0, 255, 255)
    For Each rng In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)
        For i = 1 To rng.Characters.Count
            If rng.Characters(i, 1).Font.Color = OldColor Then
                rng.Characters(i, 1).Font.Color = NewColor
            End If
        Next i
    Next rng
    Application.ScreenUpdating = True
End Sub