Conditional Formatting Formula

[spydey]

Hello (once again)!

I am using some conditional formatting to highlight specific dates in a column based upon a comparison to the TODAY() function.

The formula I am using in the conditional formating is:

=AND($F2:$F10>TODAY(),$F2:$F10<$A$1)

It is supposed to turn it yellow. It doesn't.

However, if I use just:

=$F2:$F10>TODAY()

It turns yellow.

Also, if I use:

=$F2:$F10<$A$1

It turns yellow.

But if I try and use the AND() function, no colors at all.

This is just a test population until I get it right and then transfer it over to the actual population.

Any thoughts as to why it isn't working?

I have done this before and it worked fine, but now it doesn't seem to work as expected.

Thanks a million!!

-Spydey

 

[Marcelo Branco]

Select F2:F10 being F2 the active cell and try this formula in Conditional Formatting
=AND($F2>TODAY(),$F2<$A$1)
pick the format you want

M.

 

[spydey]

@ Marcela Bronco

Looks to have worked!! Thanks a million man.

Question for you? Why was it working with part of the formula, even if I had the whole range selected, but not for the whole formula if I had the whole range selected? I know that I have done it before likes this and it worked, so I am curious as to why it didn't now .....

Thanks again for your assistance!!

-Spydey

 

[spydey]

Now to figure out how to get my conditional formatting to by dynamic ........ I think I will need to have a dynamic name referencing my range, possibly using OFFSET, and then use that dynamic named range in my conditional formatting formula so that as the data in the range grows, the dynamic name grows with it, and thus the conditional formatting follows suit ..... gotta get back to tinkering ... hehehehe

:D

-Spydey

 

[Marcelo Branco]

@ Marcela Bronco

Looks to have worked!! Thanks a million man.

Question for you? Why was it working with part of the formula, even if I had the whole range selected, but not for the whole formula if I had the whole range selected? I know that I have done it before likes this and it worked, so I am curious as to why it didn't now .....

Thanks again for your assistance!!

-Spydey

In CF when you select the entire data range you should enter the formula for the first cell. Excel automatically adjust the references to the cells below.
That's it

M.

 

[spydey]

Understood. I was used to using Excel 2007, which I don't think, at least based upon my previous experience, did this as I recall that I had to select the whole range (as in my examples above) ... but I could be wrong. I am now on Excel 2013.

Thanks for the clarification!

-Spydey

 

[Marcelo Branco]

It would work, for sure, also in XL 2007

M.

 

[spydey]

It would work, for sure, also in XL 2007

M.

Good to know. Thank you sir!

-Spydey