You can do this for the whole workbook in about 10 lines of VBA, but there's a catch.
Here's the code:
Code:
Sub blah()
With Application
.ReplaceFormat.Clear
.FindFormat.Clear
.FindFormat.Interior.Color = RGB(220, 230, 241)
.FindFormat.Locked = True
.FindFormat.FormulaHidden = False
.ReplaceFormat.Interior.Color = RGB(253, 233, 217)
Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True, FormulaVersion:=xlReplaceFormula2
.ReplaceFormat.Clear
.FindFormat.Clear
End With
End Sub
Now for the catch. The above code will either work on just the active sheet, or the whole workbook, and which it does is dependent on the last setting used in the
find (or
find and replace) dialogue box for the field
Within:.
This setting is only reset when Excel is closed down and restarted where it takes on the
Sheet value in that field (or when someone changes it and executes a find in the normal user interface). Otherwise, it remembers what the setting used was.
So the trick is to execute a Find using the
Workbook setting in that dialogue before running the code:
2023-01-24_200318.png
There is no known vba code/argument for setting that.
Then run the code on that workbook. All the appropriate cells' colours should change.