Conditional Formatting Problem - Dynamic Formula??

[GoBucks]

I am trying to figure out a complex CF formula that will format a section of
a row based on mutltiple conditions. Here is a sample table of type of data I
am working with:

Week# 14 15 16 17 18 19 ... 35
Date 4/3 4/10 4/17 4/24 5/1 5/8 12/31
Row1 10 10 10
Row2 100 100 100 100
Row3 50 50

In each row, I would like to cond. format the cells that CONTAIN values from
Week#'s 14 -16 ONLY IF there are NO values present from Week #17 thru Week
#35. This is assuming that the current week = Week 14. For example:

Row1: CF CF CF
Row2: no CF (values present after WK 16)
Row3: CF CF

When in Week 15, I would like the CF range to be from WK 15 to WK 17 with NO
values from WK 18 on. I have a cell on the sheet that used for the current
date input that I was trying to use as a cell reference for a formula. I've
been struggling with this for a few days. Any help is greatly appreciated.

 

[Pete_UK]

Setting up a mock-up for your first table, I've used two header rows
so what you refer to as Row 1 is actually row 3, and the 14 is in cell
B1 with the first date in B2. Select cell B3 and click on Format |
Conditional Formatting and choose Formula Is rather than Cell Value Is
in the first box, then put this formula in the next box:

=AND(TODAY()<B$2+21,TODAY()>=B$2,B3<>"",COUNTIF(E3:$W3,"<>")=0)

Click on the Format button, then on the Patterns tab (for background
colour) and choose your colour. Click OK twice to exit the dialogue
boxes.

Then use the Format Painter icon to copy that format down and across
your data as required.

Hope this helps.

Pete

 

[GoBucks]

Thank you Pete! I'm not sure if I'm doing something wrong but after I Format
Painter of cell B3 and go down and then across, only the cell B3 and B5 are
formatted. Cells C3, D4 and C5 do not get formatted. Any suggestions??