Conditional Format Based on specific text in 3 different cells

**tradersteve** · 10-07-2010, 10:57 PM

I have the following code to fill Y2 based on the text in L2 or M2

Please Login or Register  to view this content.

I would like to add to the formula so that it would only trigger IF the text in C2 which is normally a number followed by a space and then a single letter (ex.= 23 A), has either "rt" or "rs" at the end. (ex. = 23 A rt)

Is this too many conditions to nest within regular conditional formatting?

Any help with this or an alternative formula would be greatly appreciated!

Thanks

**mdbct** · 10-07-2010, 11:33 PM

Conditional formatting can handle the same amount of input as a regular formula.

Please Login or Register  to view this content.

**tradersteve** · 10-08-2010, 02:18 AM

Glad to know it can work but I modified the formula as suggested and it didn't work as I was wanting. Probably missed in my explanation.
I want the formatted fill to trigger IF C2 ends in "rt" or "rs" AND either L2 or M2 = "g". I tried moving the rt around to different positions within C2 but never could get it to trigger the shading.
Also, since the number in the alpha numeric combination within C2 will range from 1 to 1000 would it be easier (or necessary) to place my "rs" in front of the number instead of behind it?

I have attached a sample workbook showing my existing formula and the desired result that I forced with individual cell shading below the shaded band on the worksheet.
Thanks for the help!

**mdbct** · 10-08-2010, 06:41 AM

I must apologize. I had LEFTs instead of RIGHTs. The correct formula should be:

Please Login or Register  to view this content.

**tradersteve** · 10-09-2010, 01:22 PM

Works GREAT! Thanks!

**NicParham** · 11-08-2011, 06:49 AM

Hey this is great mdbct! I've been struggling to work out how to do this for some time. But I have an associated question: I want to shade a cell A2 where a corrsponding cell C2 contains text that includes a certain word. e.g. cells in column C contain such text as "TT LCT UN 20" and "HFW LCT HN 19". I want to format all cells in column A when the corresponding cell in column C contains "HN". So I cannot use the LEFT or RIGHT as above as my text is in the middle, and not always in the same place.

Thanks