Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-20-2023, 04:33 AM
Marcia's Avatar
Marcia Marcia is offline Select fill colors in a workbook, change to another color Windows 11 Select fill colors in a workbook, change to another color Office 2021
Expert
Select fill colors in a workbook, change to another color
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default 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.
Reply With Quote
  #2  
Old 01-22-2023, 02:42 PM
Guessed's Avatar
Guessed Guessed is offline Select fill colors in a workbook, change to another color Windows 10 Select fill colors in a workbook, change to another color Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
Reply With Quote
  #3  
Old 01-22-2023, 04:48 PM
Marcia's Avatar
Marcia Marcia is offline Select fill colors in a workbook, change to another color Windows 11 Select fill colors in a workbook, change to another color Office 2021
Expert
Select fill colors in a workbook, change to another color
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Reply With Quote
  #4  
Old 01-24-2023, 01:06 PM
p45cal's Avatar
p45cal p45cal is offline Select fill colors in a workbook, change to another color Windows 10 Select fill colors in a workbook, change to another color Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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.
Reply With Quote
  #5  
Old 01-29-2023, 06:48 AM
Marcia's Avatar
Marcia Marcia is offline Select fill colors in a workbook, change to another color Windows 11 Select fill colors in a workbook, change to another color Office 2021
Expert
Select fill colors in a workbook, change to another color
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Quote:
Originally Posted by Guessed View Post
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, I tried this code and it works but it keeps on running unless Esc is pressed. A debug error appears on the "End If" line. Thank you.

Last edited by Marcia; 01-29-2023 at 06:52 PM.
Reply With Quote
  #6  
Old 01-29-2023, 07:26 AM
Marcia's Avatar
Marcia Marcia is offline Select fill colors in a workbook, change to another color Windows 11 Select fill colors in a workbook, change to another color Office 2021
Expert
Select fill colors in a workbook, change to another color
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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
Thank you for the code p45cal and the screenshot of the Find/workbook setting. Had there been no picture, I wouldn't have understood your next instructions about "within worbkbook."
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.
Reply With Quote
  #7  
Old 01-29-2023, 08:19 AM
p45cal's Avatar
p45cal p45cal is offline Select fill colors in a workbook, change to another color Windows 10 Select fill colors in a workbook, change to another color Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by Marcia View Post
I'm trying to find the cause of the different result.
very likely to be not quite the right color (rgb value).
Reply With Quote
  #8  
Old 01-29-2023, 06:40 PM
Marcia's Avatar
Marcia Marcia is offline Select fill colors in a workbook, change to another color Windows 11 Select fill colors in a workbook, change to another color Office 2021
Expert
Select fill colors in a workbook, change to another color
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Quote:
Originally Posted by p45cal View Post
very likely to be not quite the right color (rgb value).
I cleared all the formats of the rebel cells then ran the code and got the right color. Yay. Thank you.
Reply With Quote
Reply

Thread Tools
Display Modes


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
Select fill colors in a workbook, change to another color Change accent theme color used as shape fill (not RGBs) preetidb PowerPoint 4 12-30-2013 03:22 PM
Select fill colors in a workbook, change to another color Select a range in one one workbook while working in other workbook Slow&Steady Excel 1 02-21-2010 03:34 AM
Select fill colors in a workbook, change to another color Change Automatic Fill Color Leanne PowerPoint 1 11-04-2009 08:34 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:02 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft