Search Not Just Numbers

Tuesday 22 December 2015

Excel Tip: Merry Christmas with a quick macro to swap one colour for another

First of all I would like to wish all of my readers and subscribers, a very Merry Christmas and a Happy 2016.

I know we all work hard, and I hope, like me, you all get a chance to spend some time with those you love in the coming days, whether Christmas is a festival you celebrate or not.

One hassle I sometimes have when building spreadsheets for others is changing colours.

The spreadsheet may work perfectly, but the end user might not like the colours I have chosen. This isn't too much of a problem if we are talking about large blocks of colour, but it can be a time-taking process, if I have used yellow to denote input cells throughout the whole worksheet, and the client wants orange, for example!

When this happened recently on a particularly complex spreadsheet, I decided to write a little macro to help, which turned out to be pretty straightforward and does the job in seconds.

Here is the code of the macro.
Sub ColourSwap() 
Dim Source As Variant
Dim R As Integer
Dim G As Integer
Dim B As Integer
Dim NewColour As Variant
Source = ActiveCell.Interior.Color
If MsgBox("Switch to no colour?", vbYesNo) = vbYes Then
NewColour = 0
For Each cell In ActiveSheet.Cells
If cell.Interior.Color = Source Then cell.Interior.ColorIndex = NewColour
R = InputBox("R?")
G = InputBox("G?")
B = InputBox("B?")
NewColour = RGB(R, G, B)
For Each cell In ActiveSheet.UsedRange.Cells
If cell.Interior.Color = Source Then cell.Interior.Color = NewColour
End If 
End Sub
The easiest way to include it in your spreadsheet is to hit Alt-F8 and type the name you want to call the macro in the box at the top of the dialog. I have called it ColourSwap. Click create and you will see the green sections above already showing in the window (with your chosen name replacing ColourSwap). Just paste the yellow section in between.

You can run the macro by hitting Alt-F8 again, selecting it and choosing Run. If you prefer, you can click options instead of Run, and assign a shortcut key to run it in future.

The macro works as follows, and is applied to a single worksheet at a time.

  1. Click on one of the cells that contain the colour that you wish to swap;
  2. Run the Macro
  3. You will be asked if you wish to "Switch to no colour?", if you select "Yes", then all of the cells on that worksheet that are the same colour as the cell you selected, will have any Fill Colour removed. This is the same as choosing No Fill if you were colouring the cell manually.
  4. If you select "No", you will be faced with 3 prompts, requesting the R, G and B values for the colour you wish to swap to. Once you enter these, all of the cells on the worksheet that are the same colour as the cell you selected, will be filled with this new colour.
NB: The macro only checks the Used Range of the worksheet, so will not change any coloured cells below, or to the right of the last populated cell on the worksheet. This is to save time, as it can take quite a while to loop through every cell, when this is not necessary. If (for some reason) you have coloured cells outside of the Used Range, then these should be very easy to change manually, as they will almost certainly be in a large block.

Note that if you wish to save the Macro with the spreadsheet, you will need to save the Workbook as Macro-enabled Workbook, however this will usually be unnecessary, as once the colours have been changed, the macro is no longer needed, so that you can let it save without the macro.

I hope you find this useful, and could maybe use it to make some tired old spreadsheets look a bit more festive!

Merry Christmas!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".


  1. Now, where do I get the RGB values from if I require a pale green, or pale yellow or full on red?

    1. click the Fill Colour dropdown, then More Colours, then select the colour you want, then click Custom. RGB values are shown for that colour
      no, you don't want to have to do this on a regular basis


  2. use Styles?
    or if you've not been so proactive (and who is?), use Find and Replace where, when the Options are expanded, there is the ability to replace one format with another (leave the Find what: and Replace with: boxes blank and the contents will be unaffected)


    1. Jim

      I use Find and Replace all of the time and never realised that you could do it with formats! You learn something new every day!

      Presumably it has to be the whole format rather than just the colour, which might not work in every scenario?

    2. no, you can enter partial formats,
      you can even sample an existing one from a cell then edit out the attributes you don't care about
      really quite a powerful (underused and little-known) technique


    3. That's excellent! I'll have to have a play with that! No matter how much you think you know Excel, there's always so much more to learn! Thanks, and Merry Christmas.