Link to home
Start Free TrialLog in
Avatar of Aaron Roessler
Aaron Roessler

asked on

Excel VBA to Find and Replace Font Color

This must be simple but for Mac Excel it seems more difficult.  

I just need to create a Macro that replaces all text color that is 7811599

Here is the general idea...

If Font.Color = 7811599 Then 

Replacement.Font.Color = RGB(0, 51, 160)


GRO RO.xlsx


ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Aaron Roessler
Aaron Roessler

ASKER

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.

User generated image
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

Open in new window

hmmm nothing happens. ?
Ah Ha.... Cell with two different font sizes is the issue.
https://www.loom.com/share/7fb729c662644352ad83961cab528d60
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

Open 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