Conditional formating with TEXT
 

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).

Conditional formating with TEXT
 

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","L 06","L07","L08"},0)))

This formula is the negation of

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

which describes your condition for retaining the default format.

HTh
Kostis Vezerides


Steve M Wrote:
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).


--
vezerid
------------------------------------------------------------------------
vezerid's Profile: http://www.excelforum.com/member.php...o&userid=28481
View this thread: http://www.excelforum.com/showthread...hreadid=513989

Conditional formating with TEXT
 

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)

"Steve M" <Steve wrote in message
...
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).

Conditional formating with TEXT
 

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Steve M" <Steve wrote in message
...
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).

Conditional formating with TEXT
 

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.

"vezerid" wrote:


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","L 06","L07","L08"},0)))

This formula is the negation of

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

which describes your condition for retaining the default format.

HTh
Kostis Vezerides


Steve M Wrote:
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).


--
vezerid
------------------------------------------------------------------------
vezerid's Profile: http://www.excelforum.com/member.php...o&userid=28481
View this thread: http://www.excelforum.com/showthread...hreadid=513989