VBA format cell based on fill color and font color

Question

I have an excel where I have to

remove cell fill color if existing fill color is yellow
set cell text color back to black only if existing font color is red.

I have written a macro that simply loops over each cell and checks the font color / fill color

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
...
For Each Cell In ws.UsedRange.Cells
   If Cell.Font.ColorIndex = 3 Then
      Cell.Font.ColorIndex = 0
   End If
   If Cell.Interior.ColorIndex = 6 Then
      Cell.Interior.Pattern = xlNone
      Cell.Interior.TintAndShade = 0
      Cell.Interior.PatternTintAndShade = 0
   End If
Next

It works as expected but it runs very slowly probably because it goes through each cell. Is there a to make this work faster ? I tried using conditional formatting with VBA but it cant seem to check for the cell color / cell font color...

You can try ws.UsedRange.SpecialCells(xlCellTypeConstants).Cells ... Assuming there are no formulas in the cells, you can change this in the If For Loop. — Mikku
Also adding Application.ScreenUpdating = False in the beginning of the code and True in the end will make it significantly faster. — Mikku

Siddharth Rout Siddharth Rout · Accepted Answer · 2019-08-28T04:30:24

No Need to loop. You can search and replace using colors. Try this

Remove cell fill color if existing fill color is yellow

With Application.FindFormat.Interior
    .PatternColorIndex = xlAutomatic
    .Color = 65535
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
With Application.ReplaceFormat.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True

If you had to do this manually, you would have done this

Similarly for the font.

Set cell text color back to black only if existing font color is red.

With Application.FindFormat.Font
    .Subscript = False
    .Color = 255
    .TintAndShade = 0
End With
With Application.ReplaceFormat.Font
    .Subscript = False
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
End With
Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True

If you had to do this manually, you would have done this

Note: The VBA Find uses parameters. Except What:=, rest of the parameters are optional. It is recommended that you provide those parameters. If you don’t, then Find will use the existing settings. If you do not want to provide the optional parameters then it is a must to clear those parameters before you use Find else you will get undesired results. You can do that by Application.FindFormat.Clear

Similarly Replace uses parameters and if you do not want to provide the optional parameters then clear them using Application.ReplaceFormat.Clear

VBA format cell based on fill color and font color

1 Answers