Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Replacing Background Colors in Cells.
by Allen Wyatt
(last updated October 10, 2018)
Jozef is looking for a way to change background colors of specific cells in the worksheet he created. For example, he would like to find all red background cells and change them to blue, or find all yellow backgrounds and change them to blue. Jozef wonders if there is an easy way to do this.
It's fairly obvious that you can change the background colors of any cells manually, so there is no need to go into that option for making the changes. What you need is a way to make changes to all the cells at once. If you are using Excel 2002 or 2003 you can follow these steps:
Figure 1. The Replace tab of the Find and Replace dialog box.
Figure 2. The Patterns tab of the Find Format dialog box.
If you are using an older version of Excel, these steps won't work. Instead you'll need to use a macro to do the changes. The following is an example of one that should work. (You can also use this macro in later versions of Excel, as well.)
Sub ChangeColor() Dim rCell As Range If Selection.Cells.Count = 1 Then MsgBox "Select the range to be processed." Exit Sub End If For Each rCell In Selection If rCell.Interior.Color = RGB(255, 0, 0) Then 'red rCell.Interior.Color = RGB(0, 0, 255) 'blue End If Next rCell End Sub
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9042) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Replacing Background Colors in Cells.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
When preparing a report for others to use, it is not unusual to add a horizontal line between major sections of the ...Discover More
Excel makes it easy to add all sorts of lines around a cell or range of cells. How easy? This tip demonstrates how to add ...Discover More
Need to get rid of the borders around a cell? The shortcut in this tip can make quick work of this formatting task.Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.