Conditional formatting based on another cell; apply to rows

[Solved/Closed]
Report
-
Posts
1
Registration date
Thursday February 19, 2015
Status
Member
Last seen
February 20, 2015
-
Hello,

I have two columns. Column A is for expiration dates, and Column B has a drop down menu indicating the status of an request (eg, pending, active, closed).

Now I already have conditional formatting applied to column A so that if the date entered is within 60 days of the current date, the cell is yellow. If the date entered has passed, the cell is red.

If the expiration date (Column A) has passed AND the status (Column B) is "closed", I want the cell to remain white.

The formula I have used is simple: =B2="Closed" and it is applied to A2.

The problem I am having is that I want the same formula for each row. But when I apply the rule to A2:A20, it only looks at B2 for the word "Closed" instead of B2 through B20. I also tried changing the formula to =B2:B20="Closed" but that returned the same result.

I don't know why I can't figure this out, but I just can't. Can someone help?

Thanks.

Excel 2010

1 reply

Posts
35
Registration date
Wednesday February 18, 2015
Status
Member
Last seen
March 13, 2015
7
I'd play with the conditional format statement to see if changing an absolute cell reference fixes it. When Excel writes them, it looks to set up with a format such as $A$1 which is an "absolute" cell reference to A1. If you make it $A1 instead, as you copy it down a column, it will refer to the cells in column A only but each row will increment as it is copied down.
Posts
1
Registration date
Thursday February 19, 2015
Status
Member
Last seen
February 20, 2015

yes, I was just figuring this out as you replied. you are absolutely correct. thanks!