Excel VBA to Find and Replace Font Color

Nice... this is great thank you... a lot more VBA than i thought!

I created an image (before / after) for you too see the color difference.

a lot more VBA than i thought!

I could have wimped out and given you three lines of code starting with For Each cel In Selection.Cells, but chose to restrict the search to those cells that actually contained values. I also gave you the option of pre-selecting a range of cells or letting the macro apply to the entire worksheet (if only one cell was pre-selected).

Brad

Revisiting this....  I noticed in one of my Excel files the VBA script did not change all of the blue titles to the new blue, which tells me that maybe I have multiple blue colors?
You will notice in this file that after the Macro is ran Rows 1, and 25 do not change. So maybe we could ignore looking for the 7811599 and just change any font that is greater that size 8pt ?
Here is a video with more explanation... https://www.loom.com/share/91cdc9eba83f483cadf1981df498c037

Also, just realized I need to change the ( .VerticalAlignment = xlBottom) for all those blue header titles as well.

WOW RO System.xlsx

Sub UpdateFontColor()
Dim cel As Range, rg As Range, rgC As Range, rgF As Range
Set rg = Selection
If rg.Cells.Count = 1 Then Set rg = rg.Worksheet.UsedRange

On Error Resume Next
Set rgC = rg.SpecialCells(xlCellTypeConstants)
Set rgF = rg.SpecialCells(xlCellTypeFormulas)
If Not rgC Is Nothing Then
    Set rg = rgC
    Set rg = Union(rg, rgF)
Else
    Set rg = rgF
End If

For Each cel In rg.Cells
    If cel.Font.Size > 8 Then
        cel.Font.Color = RGB(0, 51, 160)
        cel.VerticalAlignment = xlBottom
    End If
Next
End Sub

Select allOpen in new window

hmmm nothing happens. ?
Ah Ha.... Cell with two different font sizes is the issue.
https://www.loom.com/share/7fb729c662644352ad83961cab528d60

BINGO!!!

BTW, if you used the Styles feature for your section headings, you could change the font color everywhere the Style was used just by manually editing the Style. It's an exceedingly powerful and woefully overlooked feature.

oooh... thats great... I love Global settings! This is the first time we are printing these with this blue color so I never had to worry about color in general. but great to know this feature.  As a web designer I know how important global styling is. Thanks !!!

I found an Excel file that one of the Text headers was not affected by this line of code but all the other headers in this file were updated.

If cel.Font.Color <> 0 Then

Select allOpen in new window

The very last Blue Text Header of this file is not being affected by this script. Any idea why?
"F - 2000 Digital Inline Flowmeters (RT = Flow Rate and Totalizer)"
Blue White Flowmeters.xlsx