Forum Discussion

waygar's avatar
waygar
Brass Contributor
Feb 07, 2023

Search & remove fills

How can I search for all cells in say the range A:N for fill colors of RGB(255,255,0) and RGB(255,192,0) and replace them with no fill. The sheet can contain thousands of rows and so a quick way say with autofiltering might be the preferred way?

Thank you.

Macros and VBA

1 Reply

Sort By
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Feb 07, 2023

    waygar 

    Here are some links on how to do it with filters.

    Filter by font color, cell color, or icon sets

    How to find and replace fill color in Excel?

     

    or...With VBA, first what ColorIndex your corresponding cell colors correspond to.
    You can output the ColorIndex like this:

    Sub read()
MsgBoxRange("A1").Interior.ColorIndex 'example cell
End Sub

    Then you could take out the colors like this:

    sub color_out()
Dim rngRange As Range
Dim rng Cell As Range
With Sheets("Sheet1") 'Customize sheet
Set rngRange = .Range("A1:A4") 'Adjust range
End With
For Each rngCell In rngArea
If rngCell.Interior.ColorIndex = 3 _
Or rngCell.Interior.ColorIndex = 33 Then
rngCell.Interior.ColorIndex = xlNone
End If
Next rng cell
end sub

     

    Hope I could help you with these information / links.

     

    NikolinoDE

    I know I don't know anything (Socrates)

    Ich weiß das ich nichts Weiß (Sokrates)

Resources