CODEWITHSUNDEEP
excelvba
user2976568
user2976568

Reputation: 149

VBA format cell based on fill color and font color

I have an excel where I have to

  1. remove cell fill color if existing fill color is yellow

  2. 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...

Upvotes: 2

Views: 2875

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149305

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

enter image description here

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

enter image description here

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

Upvotes: 9

Related Questions