Change the background of one google sheets column based on the date in another column

0
votes

I've seen lots of answers for various other conditions under which to do this. However I can't seem to modify any of these to work for a date range. What I want is for the column color of column E to change if the date in column H is between today and 5 days from today.

I've tried varying versions of this formula: H3:H150 =today() +5 with no success

This doesn't give me the range of between now and 5 days from now but I could do multiple rules like this and just go down on each one(=today() +4, =today() +3, etc) but obviously I need this rule to work first.

Thanks!

1
google-sheetsgs-conditional-formatting
Instead of equality comparator you should use => and =< and the AND() function. – Rubén

1 Answers

0
votes

Conditional formatting is more helpful than you seem to be expecting. Clear formatting from the column to be formatted, select it and Format, Conditional formatting..., Format cells if... Custom formula is and:

=and(H1>today(),H1<today()+6,H1<>"")

Then select fill of choice and Done.

This should format the next five days (change the angled brackets around for the past five days).

Setting the range is achieved with selection of the column to be formatted (not the one with dates in it - H). If only to apply to Rows3:150 (and in general it is a good idea, for speed of processing, to restrict the ranges to which CF is applied) then either start by selecting Rows3:150 in the column to be formatted (instead of the entire column) or adjust the Apply to range for the rule, but in either case use H3 in place of H1 in the formula above.

CF should automatically apply the rule as written to the first row in the selected range and then adjust it for the second and subsequent rows in the way copying down would adjust the formula (if at all) were it in a cell in the sheet.