How to change background color of a cell after replace in MS Excel 2007?

Problem:

1. Want to change background color of a cell after it is replaced with other data?
2. Writing a macro to find and replace cells data in MS excel 2007 and don’t know how to change background color of those replaced cells?

If you have above problems, follow below steps.

Steps:

1. Open your excel document and Press CTRL+F
2. Select Replace tab
3. In Replace with: option, click on “Format…” (see below figure)
4. Goto Fill tab
5. Select the color (I selected yellow) with which you want highlight the color of replaced cells
6. Click “OK” button. Find and Replace window looks like below

So, now, in this excel sheet, if you try to replace the content of a cell , then background color of the replaced cells change to yellow color (in this example)

Note: 1.The above steps are applicable to the excel you are working on. If you open other excel file, do the same steps mentioned above.

2. In Replace format window,you have many options to change replaced cell format (changing background color, border,font etc).

Let us see how to write a macro to change background color of a cell after it is replaced with some other data using Find what and Replace with options.

Steps:

Below is the VB code to find and replace cells data

By Default, in second statement, the parameter ReplaceFormat is set to False. If it is False, then even if you run the macro to find and replace cells data, it doesn’t work. Set it to true, then whatever the format changes we did above gets reflected.

1. strFound = Cells.Find(What:=”findText”, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _

xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _

, SearchFormat:=False).Activate

2. Replace What:=”findText”, Replacement:=”replaceText”, LookAt:= _

xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=True