Using wildcards with logical (IF, AND) conditional formatting

I have two columns: A & B. Assume A contains a combinaton of numbers and
letters (e.g. 13.00.10i.022) and is formated as "General." Column B is a
whole number between 1 and 3. I would like to create a Conditional Format
formula that says "IF B=1 AND A does NOT begin with the number "12", then
color that ROW red. Continuing, "IF B=1 AND A DOES begin with the number
"12," then do nothing (no color format). How do I do this?

Thanks !

CFFormula is =AND(B1=1,LEFT(A1,2)"12")

The no color part takes care of itself.


Gord Dibben MS Excel MVP

On Fri, 5 Feb 2010 14:11:02 -0800, olddog
wrote:

I have two columns: A & B. Assume A contains a combinaton of numbers and
letters (e.g. 13.00.10i.022) and is formated as "General." Column B is a
whole number between 1 and 3. I would like to create a Conditional Format
formula that says "IF B=1 AND A does NOT begin with the number "12", then
color that ROW red. Continuing, "IF B=1 AND A DOES begin with the number
"12," then do nothing (no color format). How do I do this?

Thanks !

Hi,

Suppose your data starts in A1 and by row you mean A1:B1, not A1:XDF1:


In 2003:
1. Select the cells you want to format
2. Choose Format, Conditional Formatting
3. Choose Formula is from the first drop down
4. In the second box enter the formula:
=AND($B1=1,LEFT($A1,2)"12")
5. Click the Format button
6. Choose a color on the Patterns tab (or any available option)
7. Click OK twice.

In 2007:
1. Highlight all the cells on the rows you want formatted
2. Choose Home, Conditional Formatting, New Rule
3. Choose Use a formula to determine which cell to format
4. In the Format values where this formula is true enter the following
formula:
=AND($B1=1,LEFT($A1,2)"12")
5. Click the Format button and choose a format.
6. Click OK twice



--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"olddog" wrote:

I have two columns: A & B. Assume A contains a combinaton of numbers and
letters (e.g. 13.00.10i.022) and is formated as "General." Column B is a
whole number between 1 and 3. I would like to create a Conditional Format
formula that says "IF B=1 AND A does NOT begin with the number "12", then
color that ROW red. Continuing, "IF B=1 AND A DOES begin with the number
"12," then do nothing (no color format). How do I do this?

Thanks !