Range Selection based on Date and cell.value

[stevedhawes]

Hi all,

Hoping someone can help me here, as im stumped.

I basically want to select a range of cells and make their value = "Booked", but the range will be based on the date. So all cells less than today (and those below it) should say "booked" only.

heres an example - so when a marco runs, it takes todays date and says make the cells value in range A1:G6 for all items less than or equal to today = booked. Note the date is only listed in row 1 though, so an offset of some kind is required i think.

BEFORE RUNNING THE MACRO

A B C D E F G
1 6/11/2013 7/11/2103 8/11/2013 9/11/2013 10/11/2013 11/12/2013 12/11/2013
2 2 5 4 4 45 4 4
3 2 4 5 6 3 0 8
4 3 4 5 5 7 7 7
5 1 1 2 1 3 3 3
6 4 3 3 3 2 1 1


AFTER RUNNING THE MACRO - Based on todays date which is 8/11/2013

A B C D E F G
1 6/11/2013 7/11/2103 8/11/2013 9/11/2013 10/11/2013 11/12/2013 12/11/2013
2 Booked Booked Booked 4 45 4 4
3 Booked Booked Booked 6 3 0 8
4 Booked Booked Booked 5 7 7 7
5 Booked Booked Booked 1 3 3 3
6 Booked Booked Booked 3 2 1 1

Thanks in advance for your help on this one

Steve

 

[stevedhawes]

aah it didnt retain the rich text spacing, but hope you can see the gist of what im trying to achieve. thanks