Conditional Format Using AND

C

Coyote

Please look at the following and help me with this conditional format.
Column A: This column is either set to "CURR" or "PREV"

I want Column B to be shaded "Red" if Column A is "PREV" and the the Current
YEAR is 2004, "Blue" if Column A is "PREV" and the Currrent YEAR is 2005.

I tried using conditional formatting but I didn't have something correct.
First condition: Formula is =$A1="PREV"
2nd Condition: Formula is =(YEAR(TODAY()))=2004 Set format to RED
3rd Condition: Formula is =(YEAR(TODAY()))=2005 Set format to BLUE

Using the above conditions changed column B to "RED" regardless of whether
or not Column A was "PREV"

Thanks in Advance
Coyote
 
C

Chip Pearson

Since YEAR(TODAY()) = 2004 is always true (this year, at least),
your second format condition will always be true and red format
will be applied.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
C

Coyote

Thanks For the reply Chip,

I understand that at least for now the 2004 condition will always be true
but I only want the color change to happen if "PREV" is in column A, hence my
problem. I was thinking maybe condition 1 could have an AND function to
first see if Column A is "PREV" and if true then test for the YEAR ansd set
the color.
 
Top