Conditional Formatting: Highlighting cells based on weeknumber correlating to today's date.

[Bluesguy07]

So, this may sound confusing, but below is sample data and what I'm trying to accomplish.


Row 13 is the date the week began
Row 14 is the week number
Row 15 is the first statistic
Row 16 is the 2nd statistic


COLUMN A COL B COL C COL D

13 | Week Begin	4-Jul	11-Jul	18-Jul
14 | Week Number	28	29	30
15 | Statistic 1	349	51	0
16 | Statistic 2	0	0	0

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

I am trying to create a conditional that will highlight rows 15 and 16 based on the week number. The current week should be highlighted green, and the previous week should be highlighted yellow. However, I'm running into an issue correlating today's date with the week number in a formula. I know to find the week number for today, it is =weeknumber(today)). However, like I said... I can't correlate that to a conditional formula.

So, basically... using today's date, Cells C15 and C16 should be highlighted green right now and B15 and B16 should be highlighted yellow.


Please let me know if this is not clear. Idealy I'd like to have this figured out by the end of day, today.

 

[Eric W]

Try this:

Select rows 15:16. Click Conditional Formatting > New Rule > Use a Formula > enter:
=A$14=WEEKNUM(TODAY())

Click Format... and choose a green fill color.

Repeat those steps with this formula:
=A$14=WEEKNUM(TODAY())-1
and a yellow fill color.

Let me know if that works.

 

[Bluesguy07]

Yes! That works great. Thanks a bunch

 

[Bluesguy07]

Duplicate*****

 

[Eric W]

Glad to help!

 

[fewbblb]

I'm trying to replicate this in google sheets. The formula itself works, but the conditional formatting doesn't; I'm assuming it is related to the A$14 piece of this formula. I want it to apply to the full column, tried A:A and it didn't work. Any tips?

 

[Joe4]

I'm trying to replicate this in google sheets. The formula itself works, but the conditional formatting doesn't; I'm assuming it is related to the A$14 piece of this formula. I want it to apply to the full column, tried A:A and it didn't work. Any tips?

Welcome to the Board!

Your best bet is to post your question as a new, standalone question to the "General Discussion & Other Applications" forum here, instead of in the "Excel Questions" forum. You will note that from the description, you can see this is the best place to post Google Sheets questions: