Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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). |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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). |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
list two columnar values based on conditional formating (text color) | Excel Worksheet Functions | |||
VLOOKUP & Conditional Formating Help. | Excel Discussion (Misc queries) | |||
Highlighting text using conditional formatting | Excel Discussion (Misc queries) | |||
Help using Conditional Formating of Entire Rows | Excel Worksheet Functions | |||
conditional format for text | Excel Worksheet Functions |