Defining a dynamic range

[awsic]

Hello all!
I'm trying to create a dynamic range and then with that selection change the cell interior color to something other than white. What's getting me hung up is this:
1.) Row 1 contains calendar days based on a min-max value (done that already)
2.) based on today's date, highlight "1-Apr" as green (done that)
3.) based on today's date, highlight the two cells on either side of the current date another color - say yellow (done that)
4.) Select all the data under those five columns and adjust the color as necessary. (???)

So for today, cell AT1 = 30-Mar, AU1 = 31-Mar, AV1 = 1-Apr, AW1 = 2-Apr and finally AX1 = 4-Apr. Below that I have, at least today, 24 rows of date. I want to highlight AT1:AU24 yellow, AV1:AV24 green, AW1:AX24 yellow.

I found examples on how to find the last row of my data (24th row for today but will change +/- over time) but I don't know how define a range and then work with it so I can change colors.

I can't use a simple hard coded range because depending on what day in the future you run this, the columns get changed to a cell interior color representing a rolling 5-business day week.

 

[GlennUK]

If you have calculated the last row of data ( you don't say how, I'd imagine you'd got the calculation in a defined name ), then compare the row of the table to the calculated last row, to control how far down the shading will go, using Conditional Formatting. Select a large region, of more rows than the data will ever hold, before applying the Conditional Formatting.