From the Not Just Numbers blog:

Before I begin, I would like to wish a Happy New Year to all of my readers and subscribers – as this is my first post of 2016.

I am starting the year in humble mode, as I have had something very simple pointed out to me that I can’t believe I had never spotted, despite it being right under my nose!

In my last post of 2015, I showed you some code to swap one cell colour for another throughout a worksheet. I then received a comment from a reader that I only know as Jim, pointing out that Excel already has a very simple way of doing this!

It can in fact be done using Find and Replace.

This amazed me because I use Find and Replace all of the time and didn’t know you could do this!

It is simple as this:

  1. Highlight the range which you want to apply the changes to
  2. Click Find and Select (the binoculars icon) on the Home Ribbon and choose Replace from the dropdown menu
  3. Don’t enter anything in the Find or Replace boxes, but click the Format button next to the Find box and on the Fill Tab, select the colour that you wish to change
  4. Click the Format box next to the Replace box and on the Fill Tab, select the new colour that you wish to swap for the colour you selected above
  5. Click Replace All
You can even start with the colour of an existing cell, using the dropdown on the format button, however be sure to remove any other formats other than Fill colour.
The thing that really shocked me is that these two big Format buttons have been right there in front of me every time that I’ve used Find and Replace since Excel 2003! And that’s a lot of times!
In 2016, I am going to try to make sure that I constantly keep my eyes open for new ways to do things in Excel, instead of just relying on what I think I already know! And thanks again, Jim.

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”.