#1
|
|||
|
|||
Conditional formatting with AND, OR
Hi All,
I need to add conditional formatting on a cell. A requirement is: if the cell should be highlighted yellow if value contains letters A, F, or T. I’m trying to use “Format only cells that contain” -> Cell value -> Equal to -> AND (“A”, “F”, “T”), but looks the excel takes it literally ="AND(""A"", ""B"", ""C"")" What would you recommend? |
#2
|
||||
|
||||
Hi,
Here's one way. In the name manager (on the formulas tab), define a name called Letters which refers to this: ={"A","F","T"} Then, in the conditional formatting, create a new rule which uses a formula to determine which cells to format as this: =OR(ISNUMBER(FIND(Letters,A1))) Where A1 is the subject cell. This will be case sensitive. If you don't want it to be case sensitive then use the SEARCH() function instead of FIND(). |
#3
|
|||
|
|||
Thanks Colin,
It works I'm going to use this rule for a range of cell $A$2:$E$10, where $A$1:$E$3 is a header row. I assigned the formula =OR(ISNUMBER(FIND(Letters,A1))) to the range - and it's working. I don't understand that. FIND(find text1, within text2) - the function returns position number of text2 in text1. My A1 contains some text without T, F, A, and when I enter a, f, or t in $A$2:$E$10 I'm getting formated text. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Conditional Formatting | namedujour | Excel | 3 | 08-25-2011 01:46 PM |
help with conditional formatting | Snvlsfoal | Excel | 3 | 07-03-2011 11:55 PM |
* Conditional Formatting MS 2010 | djreyrey | Excel | 3 | 06-03-2011 01:54 AM |
Conditional formatting of Today +21 days? | SHERMAN | Excel | 3 | 12-20-2010 08:08 AM |
Challenge!! Need help though. (Conditional formatting) | knuckles70 | Excel | 2 | 02-05-2010 12:24 PM |