Previous Topic Next Topic

Conditional formatting based on another cell; apply to rows

Solved/Closed
radiofriendlybox - 19 Feb 2015 à 18:35
radiofriendlybox Posts 1 Registration date Thursday 19 February 2015 Status Member Last seen 20 February 2015 - 20 Feb 2015 à 11:34
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

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

Similar discussions

Clear cell contents based on value of another cell range using VBA
Tbonekiller -
Tbonekiller -

6 responses
Conditional Format Cells Based on Another Cell and Own Cell
Elaine -
x -

1 response
help me to add particular cells
mathewmunna -
mathew munna -

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

1 response
Conditional formatting based text in another cell
y88y -
y88y -

2 responses