#1
|
||||
|
||||
Select fill colors in a workbook, change to another color
Hi. I would like to ask help for a code that selects all fill colors RGB (220, 230, 241) in a workbook saved as "ALL FUNDS DECEMBER 2022.xlsm" then change them to RGB (253, 233, 217).
Every year, I change the fill colors that I use in our agency's annual finance reports and supporting schedules and there were more than 100 cells/range to change. Thank you. |
#2
|
||||
|
||||
It would be a lot faster to customise the color palette and apply that to your cells. In the future you just change the color in the palette.
This macro has two parts. The first part is searching all cells to find the RGB values and assign the first accent color. The final line changes the accent color in the palette. Code:
Sub RecolorMe() Dim iCol As Long, aCell As Range, aWS As Worksheet For Each aWS In ActiveWorkbook.Sheets For Each aCell In aWS.UsedRange.Cells If aCell.Interior.Color = RGB(220, 230, 241) Then aCell.Interior.ThemeColor = xlThemeColorAccent1 End If Next aCell Next aWS 'Change the theme color ActiveWorkbook.Theme.ThemeColorScheme.Colors(msoThemeAccent1) = RGB(253, 233, 217) End Sub
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#3
|
||||
|
||||
Thank you Andrew. I spent the whole weekend manually changing the colors. I will paste code in the 2023 monthly reports and I will get back to this thread on how it goes.
|
#4
|
||||
|
||||
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 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. |
#5
|
||||
|
||||
Quote:
Last edited by Marcia; 01-29-2023 at 06:52 PM. |
#6
|
||||
|
||||
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 The code perfectly works in all the sheets except from some random cells that had not changed colors. I'm trying to find the cause of the different result. |
#7
|
||||
|
||||
very likely to be not quite the right color (rgb value).
|
#8
|
||||
|
||||
I cleared all the formats of the rebel cells then ran the code and got the right color. Yay. Thank you.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How To Select and Change Color with Keyboard Shortcuts? | abrogard | Word | 12 | 06-04-2022 12:18 PM |
Allow Cell Background Fill/Text Color Change while Protected Sheets are Grouped | RaudelJr | Excel | 5 | 04-18-2017 11:11 PM |
Change accent theme color used as shape fill (not RGBs) | preetidb | PowerPoint | 4 | 12-30-2013 03:22 PM |
Select a range in one one workbook while working in other workbook | Slow&Steady | Excel | 1 | 02-21-2010 03:34 AM |
Change Automatic Fill Color | Leanne | PowerPoint | 1 | 11-04-2009 08:34 PM |