VBA not working - change fill colour

[rob_sheeds]

Hi and thanks in advance. Got this code from another forum and it doesnt work. It changes sheets but nothing happens.
I would like to Find and Replace a fill colour using RGB, in ALL cells in ALL sheets in ALL open workbooks, regardless of any other formats on the cell.

[Sub ChangeFormats()
With Application.FindFormat.Interior
.Color = RGB(191, 191, 191)
End With
With Application.ReplaceFormat.Interior
.Color = RGB(242, 242, 242)
End With
For Each aSheet In ActiveWorkbook.Worksheets
aSheet.Activate
Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
Next aSheet
End Sub]

 

[Jerry Sullivan]

Hi Rob, That code should work to replace the fill color of any cells in the ActiveWorkbook that are currently set with Color = RGB(191, 191, 191).

It can be extended to all open workbooks, but before suggesting code for that change, let's make sure this works in the ActiveWorkbook.

Are you saying that you want to make all cells filled with .Color = RGB(242, 242, 242) even if the currently have fill colors other than RGB(191, 191, 191) including no color?

If that's the case, then you don't need to use Find and Replace, you could just apply that fill color to all cells.

Sub ChangeFormats2()
    Dim aSheet As Worksheet

    
    For Each aSheet In ActiveWorkbook.Worksheets
        aSheet.Cells.Interior.Color = RGB(191, 191, 191)
    Next aSheet
End Sub

Please clarify if I've misunderstood.

 

[rob_sheeds]

Hi and thanks. I actually need to find all cells with that colour and replace it with the new colour. Testing this using Ctrl F gives unwanted results. Only when I click "format" "choose format from cell" and actually choose the colour I want to find, will it replace with a given RGB. Thinking its a bug possibly?
Either way I would like to Find and Replace.
THanks

Hi Rob, That code should work to replace the fill color of any cells in the ActiveWorkbook that are currently set with Color = RGB(191, 191, 191).

It can be extended to all open workbooks, but before suggesting code for that change, let's make sure this works in the ActiveWorkbook.

Are you saying that you want to make all cells filled with .Color = RGB(242, 242, 242) even if the currently have fill colors other than RGB(191, 191, 191) including no color?

If that's the case, then you don't need to use Find and Replace, you could just apply that fill color to all cells.

Sub ChangeFormats2()
    Dim aSheet As Worksheet

    
    For Each aSheet In ActiveWorkbook.Worksheets
        aSheet.Cells.Interior.Color = RGB(191, 191, 191)
    Next aSheet
End Sub

Please clarify if I've misunderstood.

 

[Jerry Sullivan]

Hmm...the code in your OP works for me to replace fill color RGB(191, 191, 191).

If it works for you when you chose the color manually, try recording that process with the macro recorder and posting that code here so we can spot why that works and your other code doesn't.

 

[rob_sheeds]

[ Application.FindFormat.Clear
Application.FindFormat.NumberFormat = "General"
With Application.FindFormat
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Application.FindFormat.Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.Underline = xlUnderlineStyleNone
.ThemeColor = 2
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Application.FindFormat.Borders(xlLeft).LineStyle = xlNone
Application.FindFormat.Borders(xlRight).LineStyle = xlNone
Application.FindFormat.Borders(xlBottom).LineStyle = xlNone
Application.FindFormat.Borders(xlDiagonalDown).LineStyle = xlNone
Application.FindFormat.Borders(xlDiagonalUp).LineStyle = xlNone
With Application.FindFormat.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
Application.FindFormat.Locked = True
Application.FindFormat.FormulaHidden = False
With Application.ReplaceFormat.Interior
.PatternColorIndex = xlAutomatic
.Color = 15921906
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
End Sub]

Hmm...the code in your OP works for me to replace fill color RGB(191, 191, 191).

If it works for you when you chose the color manually, try recording that process with the macro recorder and posting that code here so we can spot why that works and your other code doesn't.

 

[Jerry Sullivan]

Rob,

One possibility is that you already have some other .FindFormat properties set besides Color, (like Borders), and those need to be cleared before you run the code in your OP. Otherwise the macro will only try to replace formats matching all the set properties.

Try this version to see if it does what you want in the ActiveWorkbook. If so, it will be easy to extend to all open workbooks.

Sub ChangeFormats2()
    Dim aSheet As Worksheet

    
    '--clear then set Find/Replace format properties
    With Application
        With .FindFormat
            .Clear
            .Interior.Color = RGB(191, 191, 191)
        End With
        With .ReplaceFormat
            .Clear
            .Interior.Color = RGB(242, 242, 242)
        End With
    End With

    
    For Each aSheet In ActiveWorkbook.Worksheets
        aSheet.Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
            xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
    Next aSheet

    
    '--clear Find/Replace format properties
    Application.ReplaceFormat.Clear
    Application.FindFormat.Clear


End Sub

BTW, From the "[ ]" brackets wrapping your code, it looks like you're trying to use Code Tags but it isn't working.

They need to be added like this...<br />

<br />
'Paste your code here.<br />

<br />
An easy way to do that (since I always forget the direction of the slash), is to
go to the Advanced View in the forum editor, highlight your code, then click the Pound # icon.

 

[rob_sheeds]

Hi Jerry,
No it still doesn't find the colour and replace it. But its OK, I will just change all of it the ol' fashioned way. Appreciate your help though

 

[MARK858]

Silly question are the original colors set by conditional formatting?

 

[Jerry Sullivan]

Silly question are the original colors set by conditional formatting?

Hi Mark, Good question to explore that other possibility. Based on the macro that Rob recorded and posted in post #5, the range in that example started with Range.Interior formatting of RGB(191,191,191) prior to the Replace. It's possible that the cells also have Conditional Formatting, but that would be surprising.

Rob, I'd be glad to take a quick look at your example file if you would care to upload it to a hosting site and post a link, or we could exchange email addresses by PM.