Dynamically change cell range in formula by background color?
 

Have a spreadsheet with columns representing weekly data. I have
formulas which total history weekly data, and the forecasted data for
future weeks. As weeks go by I change the color of the background of
the last week to show that it is now actual history data. What is the
best way that my forumlas can be adjusted to represent this color
change without having to edit the range of cells manually? Is there a
better way to do something like this?

Dynamically change cell range in formula by background color?
 

Dim BigRange as Range, rng as Range, cell as Range
set BigRange = Range("A1").CurrentRegion
for each cell in BigRange
if cell.Interior.ColorIndex = 3 then
if rng is nothing then
set rng = cell
else
set rng = union(rng,cell)
end if
Next
rng.Name = "MyHistory"

Then in your formulas refer to MyHistory

Probably much better ways not using color, but based on the minimal
information provided

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
Have a spreadsheet with columns representing weekly data. I have
formulas which total history weekly data, and the forecasted data for
future weeks. As weeks go by I change the color of the background of
the last week to show that it is now actual history data. What is the
best way that my forumlas can be adjusted to represent this color
change without having to edit the range of cells manually? Is there a
better way to do something like this?