Conditional formating with TEXT

S

Steve M

I have three columns where entires will either be text, text with letters or
blank. If the entry is one of the following--L01, L02, L03, L04, L05, L06,
L07 or L08 I want the cell to be formatted with standard black text with a
white background. I also want this same formatting if the cell is blank. If
the cell is not blank and contains anything other than the entries I
specified above, I want the background to be black and the font to be white.
The problem I am having is getting the blanks to be formatted different from
the "other" entries (in others words, entries not specified above).
 
V

vezerid

Since the conditions you are describing are for the default format, use
CF for the case of not blank and not in {L01,..., L08}. In CF use
FormulaIs (assuming you are in cell A1)

=AND(A1<>"",
ISERROR(MATCH(A1,{"L01","L02","L03","L04","L05","L06","L07","L08"},0)))

This formula is the negation of

=OR(A1="",ISNUMBER(MATCH(A1,{"L01","L02","L03","L04","L05","L06","L07","L08"},0)))

which describes your condition for retaining the default format.

HTh
Kostis Vezerides
 
B

Bob Phillips

You could use conditional formatting with this formula

=NOT(OR(A1="",ISNUMBER(MATCH(A1,{"L01","L02","L03","L04","L05","L06","L07","
L08"},0))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
B

Bernard Liengme

I tried Formula Is =OR(E1="L01",E1="L02",E1="L03",E1="L04"
E1="L05",E1="L06",E1=" ")
and like you the 'blank's did not word
So I tried
=OR(E1="L01",E1="L02",E1="L03",E1="L04" E1="L05",E1="L06",ISBLANK(E1))
and this worked
hope this helps you
 
S

Steve M

Perhaps I did something wrong. When I entered these formulas, I received an
error messages saying I could not use an array in conditional formatting.
 
Top