I will demonstrate this with a quick example.

I have a worksheet below, named “data”. This worksheet has multiple tables set up with a light yellow fill color for all header rows and first columns, and no fill for the rest of the table.

The color scheme for my project just got updated, so I need to change the color palette from light yellow to lavender. Because all the light yellow cells are not contiguous (next to one another), I cannot select all of them at once using the Select All (Ctrl+A) function. So doing this update the manual way would require me holding down the Ctrl key and selecting all the light yellow cells, one after the other. But I do not have that much time to spare.

Here comes to ChangeOldColorToNew macro to the rescue!

Sub ChangeOldColortoNew()


For Each cell In Sheets("data").Range("$A$1:$K$1000")

If cell.Interior.Color = Range("oldColor").Interior.Color Then
cell.Interior.Color = Range("newColor").Interior.Color

End If

Next cell

End Sub

This is a simple, 5-line VBA code that automates the task of locating all the cells in my worksheet which have the old color (light yellow) and changing their color to lavender, one after the other. The whole operation takes less than 15 seconds to complete.

The first step to take is to designate two cells – one with the old color and the other with the new color – and name them in the worksheet, using the Name Manager. The cell with the old color I named “oldColor” and the cell with the new color, “newColor”. The reason for naming them was to create two reference points for the macro (notice in the code above that both named ranges are referenced) for the old and new colors. If you decide to use a different naming convention, make sure to update your VBA code to match your new names.

The next step is to set up the macro. To use this macro in my worksheet, I access the VBA Editor (keyboard shortcut Alt+F11, but make sure your Developer tab is activated on your Excel ribbon beforehand – click here to see how to do this), go to Insert in the VBA Editor pane and click Module. Copy and paste the VBA code above into the new window that opens up.

(If you have multiple Excel files open, make sure you are Inserting a new module in the right workbook by double-checking the name at the top of the VBA Editor window – it is Book4 in my example.)

Save the file as a macro-enabled workbook (.xlsm).

Now the code is set up, go to Macros (or keyboard shortcut Alt+F8, look for the macro with the name “ChangeOldColortoNew” and click “Run”. Or assign the macro to a button – and click the button to run the code.