Previous Topic Next Topic

Conditional formatting based on another cell; apply to rows

Solved/Closed
radiofriendlybox - Feb 19, 2015 at 06:35 PM
radiofriendlybox Posts 1 Registration date Thursday February 19, 2015 Status Member Last seen February 20, 2015 - Feb 20, 2015 at 11:34 AM
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 response

Answer 1 / 1
cmarzahn Posts 35 Registration date Wednesday February 18, 2015 Status Member Last seen March 13, 2015 7
Feb 20, 2015 at 11:22 AM
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.
0
radiofriendlybox Posts 1 Registration date Thursday February 19, 2015 Status Member Last seen February 20, 2015
Feb 20, 2015 at 11:34 AM
yes, I was just figuring this out as you replied. you are absolutely correct. thanks!
0

Similar discussions

How to use conditional format based on text?
Leanne99 -
Mooseman -

26 responses
Highlight cell if another cell contains specific text
Abbi -
vcoolio -

1 response
Conditional formating - apply on more cells
Biljana -
Raj -

32 responses
help me to add particular cells
mathewmunna -
mathew munna -

2 responses
Highlight cell if another cell contains any text
LvH -
LvH -

4 responses