+ Reply to Thread
Results 1 to 2 of 2

Dynamically change cell range in formula by background color?

  1. #1

    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?


  2. #2
    Tom Ogilvy
    Guest

    Re: 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

    <[email protected]> wrote in message
    news:[email protected]...
    > 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?
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1