From the Not Just Numbers blog:
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.
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
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.
- Click on one of the cells that contain the colour that you wish to swap;
- Run the Macro
- 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.
- 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.
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”.