How can Excel conditional formatting set a given format to any cell containing a predetermined text string?

Similar questions: Excel conditional formatting set format cell predetermined text string.

See below If you are using Excel 2003 it is a bit complicated. You need to use the "Formula" option from the drop-down menu in "Conditional Formatting" and use the "Find" command. If you use Excel 2007, I believe Microsoft added a new option for searching for a string.

The following explains it well: 1. Select the first cell of your list (here A4) and go to Format - Conditional Formatting... 2. Select Formula Is from the drop down, and then enter the following formula: =AND(FIND($B$1;A4)>0;$B$1"") FIND returns the position of the found match, so FIND($B$1;A4)>0 will always be TRUE if a match is found ($B$1 being the address of the cell with the search string, and A4 being the address of the current cell, which the formula is entered for).

Since FIND always returns 1 if the search string is empty, we have also to check that it's not empty with $B$1"", or all entries would be highlighted if no search string was entered. And the function AND finally combines these two checks, so that both conditions must be True to highlight a cell. 3.

Now copy the format to all other cells of the list. Note that the address A4 was written in the formula as a relative reference, contrary to $B$1, which is an absolute reference. So the reference to A4 will be adapted accordingly when copying the formula to other cells, having it always refer to the cell where the formula is.

Select A4, then go to Edit - Copy Select the entire list, then go to Edit - Paste Special... Select Formats and press OK. Unfortunately you can't copy the conditional formatting only (not without use of VBA code), but all formats. So you'll probably have to repair the layout formatting of your list afterwards.

If you enter something in the search field now, all cells that contain this string will be highlighted:http://vba-corner.livejournal.com/3962.html .

1 Thanks so much for your excellent response. I wanted to apply a light yellow background whenever the string "###" appeared anywhere in the cell. In my own application I couldn't get your suggestion to work exactly, however adjusting the formula a littledid indeed have the desired result.To sum up, I highlighted the column I wanted to format,clicked Format then Conditional Formatting, set the Condition 1 to "Formula Is", then enteredthis formula =AND(FIND("###",J1,1)) and selected the format pattern of light yellow.

It works! Most grateful for your assistance.

Thanks so much for your excellent response. I wanted to apply a light yellow background whenever the string "###" appeared anywhere in the cell. In my own application I couldn't get your suggestion to work exactly, however adjusting the formula a littledid indeed have the desired result.To sum up, I highlighted the column I wanted to format,clicked Format then Conditional Formatting, set the Condition 1 to "Formula Is", then enteredthis formula =AND(FIND("###",J1,1)) and selected the format pattern of light yellow.

It works! Most grateful for your assistance.

Paradise. T replied to post #1: 2 I am happy it helped. I wish you had rated my answer though!

:D .

I am happy it helped. I wish you had rated my answer though! :D.

I am having a formatting problem with my Excel 2007.

In excel (2003) short of clicking on a cell and checking specifically for conditional formatting, is there any way to.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.