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:
- Select the entire range that contains the red cells.
- Press Ctrl+H. Excel will display the Find and Replace dialog.
- Click the Options button to show additional options.
- Leave the Find What and Replace With boxes blank.
- On the right side, choose the dropdown next to the top Format button. Choose Format From Cell. Click on a cell with a red font.
- 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.
- Click Replace All.
- 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.