Shade the last 3 days in color .. help!

Question

Hello, I have got a range of date in one column, which i want to shade the last 3 days in blue and the rest in yellow.

e.g. If the date is from 1/7/10 to 10/7/10, i want to shade the (entire) rows with date 8/7/10,9/7/10 and 10/7/10 in blue and the rest 1/7/10-7/7/10 in yellow. (Date in column K)

I need VBA script to perform the job so that it can identify the date by itself and capable of future update.

Attached please find the reference. http://www.speedyshare.com/files/23445407/Book1.xls ( in this example, i will want the row (from F to N) of 17/7,18/7 and 19/7 to be shaded in blue and the rest row (also from F to N) in yellow)

Thanks!!

Answer 1

You don't need VBA programming for this. You can use conditional formatting on the entire row, but use a formula that refers to the cell in column K as the condition.

Select the rows you want to have highlighted so that the active cell is on the first row of the selection:

=AND($K1<=TODAY(),$K1>TODAY()-3)

Change the row of $K1 to the first row of your selection if your formatting doesn't start on row 1.

For reference, see How to apply conditional formatting in Excel.