How to Find And Replace Font Color And Background Color? Replace Background and Font colors. Find And Replace Method in Excel with Example. Find and Replace Excel 2007.
Every MS Excel user apply font or background colors to his / her excel worksheet for various purposes or reasons, like green to indicate that an record has been checked or maybe red or some other color of their choice to indicate that a value need to be reviewed. Sometimes in MS Excel we need to find the cell colors and replace that color with another color or no fill color. So, here is a question we have i.e
How to find and replace cell colors in Excel?
How a person can remove the green fill color from hundereds of cells throught the workbook without having to do them manually and without messing up the other formatting such as fonts style, borders in the same cells?
So, the easiest way to replace font and background color from cells within a worksheet or workbook in Microsoft Excel (Excel 2007) is find and replace cell formatting. Excel provides find and replace cell formatting.
Follow the steps given here to find cells with a specific fill color and replace with no fill or another color;
1) To open the Find & Replace dialog box, Press CTRL+H
2) It may be possible that 'Find what' or 'Replace with' fields contains any values from a previous search, so first clear both the fields;
3) You will need to click the Options button to expand the dialog box if you don't see the format button on the Find and Replace window.
4) Click the small arrow on the right edge of the Format... button and choose Clear Find Format; (or Clear Replace Format), If the small boxes to the left of the Format... buttons do not say 'No Format Set' (IF there is already showing No Format set then there is no need to clear find Format or Clear Replace Format).
5) Click the small arrow on the right edge of the Format button and choose Format button if you want to open the Find Format dialog box or click on 'Choose Format from cell...' if you want to select the format or text color from the cells of excel worksheet.
6) On Find Format dialog box, click the Fill tab and choose the Fill color you want to replace and click OK.
After that You will see a 'Preview' of the color you chose in the small box to the left of the Format button;
7) Next, choose the color you want to replace the 'found' color with by clicking the Format button to the right of the 'Replace with' field. Click the Fill tab in the Find Format dialog, choose the replacement Fill color you want and click OK, Alternatively you can pick format from cells of Exce worksheets.
In this example, the cells containing green fill color will be replaced by a Sky Blue fill color;
8) Click the Replace All button and all of the cells with the green Fill color will be replaced with Sky Blue Fill without affecting any of the other cell formatting such as font size, font style, font color, borders etc...
As you've probably already realized, you can utilize this same technique to easily replace any other cell formatting attribute or multiple formatting attributes.
Note:- Choose Format From Cell option on the Format button, this is useful when you want to use all or multiple parts of a cell's formatting as your search criteria. Also, if you want to find a color but you are not exactly sure which color is in the cell, this option can help. If you don't know or not sure which background color is in cell, then 'Choose Format From Cell' option, to choose the correct color along with all the other formatting. Then clear any unwanted color or formatting attributes from the criteria by clicking the clear button on each tab (i.e Alignment, Font, Border, Protection, Fill, Number).
Here is an another trick to select All Cells that contains a specific color or any other formatting. How to select the colored cells and not replace them in Excel Spreadsheet?Instead of clicking the Replace All button in the above step 8, Click the Find all button and all of the found cells will be listed at the bottom of the Find & Replace dialog box. After that press CTRL+A to select all of the items in the list. All of the cells have been selected on your worksheet after Pressing the CTRL+A button. Now click the close button and you will be returned to your worksheet with all of the found cells selected.
I hope this will help you guys, don't forget to write your feedback and suggestion in comment box, also you can share if you have any question regarding MS Excel (Excel 2007).
