Automatically highlight rows based off a number in a cell.

I believe this will be possible. you will need a different rule for each color. You will create a custom formula for the rule and create a formula that identified if any given cell in the 'Applies To' range should be highlighted. That rule must be written for the upper left most cell in the 'Applies To' range and use $ to lock columns or rows of reference that should stay the same as excel evaluates other cells in that range (and cols and row reference without $ will increment accordingly). so if the Applies To range is A1:G100 and your formula is =(A1>$A$1) then any cell in that range that is > than the value in A1 will get highlighted and a formula =(A$1>$A$1) will highlight the entire column based on the value in row 1 being > the value in A1.

if you attach the file or make it available on a share site like SharePoint we could probably help more.  Also, the formula to determine "today's calendar week" could be done based on the text you have or just based on column numbers but note if you put in 15 today it will highlight based on today and then next week that group being highlighted will shift, I just wanted to verify you realize that and didn't expect that it would magically "stick" to starting this week.  If you want that to 'stick' to a particular starting week you need that included in the table.