conditional formatting based on formula not cell value

Hi Guys,

I have been trying without a success to apply a conditional formatting on the cells in each column to colour them in if the date in one of the top cells are the same as the one in B1 [=today()]

Each column indicates the date of the beginning of each week. So I was trying to come up with the formula that will get this formatting work [if $B$1 is equal or bigger than column$3 and equal or smaller than sum(column$3+6)]

The thing that is causing me trouble is that I really don't know how to implement that formula in conditional formating.

I have attached a sample file to show you what it looks like. I was trying on cell E5 to start off but couldn't get it to work.

If any of you guys have any idea how to implement a formula in conditional formatting as it is not based on the cell value it would be great.


Thanks for reading it and giving a try.

Best Regards
Simon

Try this
Formula is:=

Please Login or Register  to view this content.

Applies to:=

Please Login or Register  to view this content.

Format.......
To suit your needs

[EDIT]
Oops! .... I read the dates in your table as Week Ending Dates, not Week Commencing.
Change the formula to

Please Login or Register  to view this content.

Hope this helps

Thank you Marcol,

It is working exactly as I wanted. Now I can highlite the whole part in each column when it is relevant to the date.

Thanks again.
Simon

Change the the range reference to
Applies to:=

Please Login or Register  to view this content.

Hi Marcol,

Could you explain how you change/apply this range reference.

I have done it for columns but I was trying to transpose it to rows and don't know how to make it change the row number automatically. And to be honest with you I have even forgot how I applied it for all of those columns.

If you would have a quick fixed answer to that it would be great. I will still be trying anyway whatever I can think of.

Thanks for any effort put.

Simon

Don't bother yourself Guys,

I have found a reason of this. In Conditional Formating I put and in small letters instead of CAPITALS and this is why it didn't pick it up.

Thanks anyway.

SImon

I don't know if I follow

.....but I was trying to transpose it to rows .....

I have extended the table to cover 52 weeks

Then with the C/F
Applies To:=

Please Login or Register  to view this content.

Is this what you mean?