This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Change All Red Font Cells to Blue Font
Problem: I’ve marked a few hundred cells in a large workbook using a red font. My manager is superstitious and wants all the red cells changed to blue. The red cells are not contiguous. I did not use cell styles to apply red.
Strategy: You can use Find and Replace to change formats. Here’s what you do:
1. Select the entire range that contains the red cells.
2. Press Ctrl+H or select Home, Find & Select, Replace. Excel will display the Find and Replace dialog.
3. Click the Options button to show additional options.
4. Leave the Find What and Replace With boxes blank.
5. On the right side, choose the dropdown next to the top Format button. Excel will offer options for Format, Choose Format from Cell, and Clear Find Format. Choose Format From Cell. Click on a cell with a red font.
6. Assuming you don’t already have a cell formatted in blue, click on the bottom Format button. Excel will display the Find Format dialog. Go to the Font tab and choose a blue color. Click OK to return to Find and Replace.
7. Click Replace All.
Figure 1219 Replace cell formatting with Find and Replace.
Results: The red fonts are changed to blue.
Gotcha: When you choose the format from an existing cell, Excel picks up all the formats. When you perform the Replace, if a format does not match exactly, the cell will not be replaced. For example, if some cells were left-justified instead of right-justified, they will not be replaced.