Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Replacing Background Colors in Cells.
Written by Allen Wyatt (last updated December 5, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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 to do is use a simple tool, such as the Find and Replace tool, to make the color changes. Follow these steps:
Figure 1. The Replace tab of the Find and Replace dialog box.
Figure 2. The Fill tab of the Find Format dialog box.
If you find yourself needing to make the same color changes over and over (for instance, always changing from red to blue), then you may be interested in using a macro to do the changes. The following example could be assigned to a shortcut key or added to the Quick Access Toolbar so you can use it quickly:
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
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9043) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Replacing Background Colors in Cells.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
A handy way to store latitude and longitude values in Excel is to treat them as regular time values. When it comes around ...
Discover MoreWant to change the size of the font within a worksheet? Excel allows you to choose from a list of sizes, as well as ...
Discover MoreAs you are formatting a worksheet, Excel allows you to easily add borders to cells. Adding rounded corners to cells is a ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-04-06 09:16:41
Allen
You are correct, Hamlet -- there is no way to do this on a Mac. The "Format" buttons (step 3) are not available in the Mac version. Perhaps Microsoft will see fit to include them one day.
-Allen
2024-04-06 09:11:17
Hamlet
Hi,
What would be the option for Excel for Mac (Office 365 subscription)? There's no 'format' option in the Replace tab in this version. Everywhere I've looked gives me the same tip, but no alternative option for Mac. Thanks!
2021-07-28 09:22:17
Ernest Striker
Dear Allen,
Thanks a lot, changing color has never been so much fun !!!
Sincerely, Ernest Striker
2020-03-25 11:01:48
James Foote
Wow. This tip is extremely powerful.
It literally saved me hours of work.
Thanks -
Jim '
2019-12-06 08:22:11
Willy Vanhaelen
@Ruben
Replace:
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
with:
For Each rCell In Selection
If rCell.Interior.Color = RGB(255, 0, 0) Then 'red
If If Not rCell.Rows.Hidden Then
rCell.Interior.Color = RGB(0, 0, 255) 'blue
End If
End If
Next rCell
2019-12-05 14:58:21
RUBEN GUTIERREZ MARTINEZ
i have a question, per say how whould you use the vba code when you have a filter in a table? I tried your code and it works but it also color all of the other cells that have the same color but i had filtered
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments