Forum Discussion

Qatsius's avatar
Qatsius
Copper Contributor
Dec 30, 2021

Changing colours of certain letters in all words

Hey,
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.

Please help!

excel

1 Reply

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Dec 30, 2021

    Qatsius 

    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

Resources