Microsoft Office Forums - View Single Post - [Solved] Select fill colors in a workbook, change to another color

Marcia · 01-29-2023, 06:48 AM

Quote:

Originally Posted by Guessed

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.