Solved: Excel Conditional Formatting question- simple

Discussion in 'Business Applications' started by singletrak, May 9, 2014.

Not open for further replies.

Joined:
May 9, 2014
Messages:
17
I have applied conditional formatting to a column of cells to highlight the cell red when the date contained in it is > 180 days. The formula applied to all of the cells is =TODAY()-180. Works perfect, however, there are cells with no values (dates) entered yet and they are also highlighted.

Ideally I'd like the cells with no date in them to remain white until a date is entered and then apply the conditions after a date is entered.

Thanks in advance for any help.

2. etafModerator

Joined:
Oct 2, 2003
Messages:
64,965
First Name:
Wayne
What cells are you using ?

=AND( TODAY()-180, Cell<>"")

Joined:
May 9, 2014
Messages:
17
The cells I'm using are I46-I57. I46 is the only cell with a date now but eventually the rest will be populated and I'd like the formula to apply to them once the date is entered. So, can you provide and example of the formula?

I have several other columns that I'll be able to apply this to. Thank you!

4. etafModerator

Joined:
Oct 2, 2003
Messages:
64,965
First Name:
Wayne
as you are going back 180 days with

I46<Today()-180

=AND( I46<TODAY()-180, I46l<>"")

can you expand on this - do you want to fill other columns based on I46 - OR - will they have a date and be independent

for 2007 or 2010 excel version
Conditional Formatting

Highlight applicable range >>
I46:I57

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:

=AND( I46<TODAY()-180, I46<>"")

Format&#8230; [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

Joined:
May 9, 2014
Messages:
17
The other columns will be totally separate. To be specific, I work in a hospital and each column represents a different lab which needs to be done every 6 months (Ex: "I" column is for liver function tests, "J" column will be for blood counts etc.). I'd like to enter the date the last lab was drawn and have it highlight when it becomes 180 days or longer since it was last done.

The blank cells represent a lab that wasn't drawn yet or just has not been filled in yet. I have to look them up indivudually and fill in as I go. I just don't want the blank cells to be highlighted. Thank you.

6. etafModerator

Joined:
Oct 2, 2003
Messages:
64,965
First Name:
Wayne
in which case the formula I provided should work ok for you

if not perhaps a sample excel sheet would help us

Joined:
May 9, 2014
Messages:
17
Sorry - Excel 2007 and they cells are independent of I46.

Joined:
May 9, 2014
Messages:
17
Works Like a charm!
Thanks!

9. etafModerator

Joined:
Oct 2, 2003
Messages:
64,965
First Name:
Wayne

Joined:
May 9, 2014
Messages:
17
The solution has been working great all day.

A new issue came up. For the lab values I need to highlight values when they are outside of a range (Ex: any value not between 30-80 need to be highlighted). I tried =ABS(C2)>D2 where C2=first value and D2=second value but that obviously incorrect.

11. etafModerator

Joined:
Oct 2, 2003
Messages:
64,965
First Name:
Wayne
when you say first and 2nd value - can you explain a little more

to get a value between 30 and 80
assume the value is in A1

then

=AND( A1>=30, A1<=80)

Joined:
May 9, 2014
Messages:
17
First value = low end of range (30) Second value = high end of range (80). I'd like any cell containing a number below 30 or above 80 to be highlighted. Also cells with no value should remain white.

Thank you so much for your time btw. You've made my work today more productive.

13. etafModerator

Joined:
Oct 2, 2003
Messages:
64,965
First Name:
Wayne
=AND( A1>=\$C\$2, A1<=\$D\$2)

The \$ make sure that when checking other cells , the C2 and D2 do not change - called absolute reference rather than relative reference

should not change if cell is blank as that would be seen as 0 and not in the range
but just incase

=AND( A1>=\$C\$2, A1<=\$D\$2, A1<>"")

Joined:
May 9, 2014
Messages:
17
=AND( A1>=\$C\$2, A1<=\$D\$2, A1<>"")[/QUOTE]

Sorry just got back to this project. I keep getting an error message with this formula.

My range is 30-80 ( numbers outside this range should be highlighted empty cells remain white) and the first cell with data is D46

So I tried =AND(D46>=\$3\$0,D46<=\$8\$0,D46<>"") Should AND be "ABS" or do I have this entirely wrong?

Thank you.

15. etafModerator

Joined:
Oct 2, 2003
Messages:
64,965
First Name:
Wayne
where C2=first value and D2=second value
so in this case
C2 =30 and D2 =80
correct ?
=AND( D46>=\$C\$2, D46<=\$D\$2, D46<>"")

As Seen On