Results 1 to 5 of 5
  1. #1
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts

    Interior color change event

    Through a PIM, Kburns posed a question in regards to the thread at:
    http://windowssecrets.com/forums/sho...erencing+Cell.
    Since this topic has been perplexing to some, I thought there might be of some benefit by posting a possible solution.

    Question:
    Is it possible to change the interior color of a cell that references a cell on another sheet by changing the referenced cell’s interior color? For example, on Sheet1 cell A1 has the value “Hello”. On Sheet2 cell C7 has the formula =Sheet1!A1 that results in “Hello”. If the interior color of Sheet1 cell A1 is changed to red, is it possible to automatically change the interior color of Sheet2 cell C7 to red as well?

    Since there are no Excel functions to return a cell’s color nor conditional formatting to detect changes in a cell’s color, we must look to VBA for the answer. Unfortunately, none of the VBA events detect a cell color change either. Seems like Microsoft dropped the ball on this one. Scouring the Internet, there are samples of custom events in class modules to monitor for Color changes in a cell’s interior but they are not reliable and require modification specific to your situation. What seems like a better solution is the approach I propose below.

    Following the Logic:
    When a cell is selected, using the Worksheet_SelectionChange event its worksheet index number and cell address are written to and stored on a hidden sheet called “Hidden” for later retrieval. While the active cell, changes to the cell may be made at this time including its value or perhaps its cell interior color. When the user clicks out of the cell to select a new cell, the old cell’s sheet and address are retrieved. A check is done to see if the old cell has any dependents (cells that reference it in a formula) and then grabs their worksheet index number and cell address. With both the old cell and the dependent cell addresses known, the interior cell color of the old cell is assigned to its dependent cell. This all occurred automatically as the user clicked out of the cell by selecting a new one. Here is the code:

    Place in the worksheet module for the sheet containing the cells being referenced:
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    '*******************************************
    '*  THE USER HAS JUST SELECTED A NEW CELL  *
    '*******************************************
    Application.ScreenUpdating = False
    '----------------------------------------
    'DECLARE AND SET VARIABLES
    Dim rng As Range, oldcell As Range
    '----------------------------------------
    'RETRIEVE ADDRESS OF PREVIOUS SELECTED CELL (oldcell)
    With Worksheets("Hidden")
        Set oldcell = Worksheets(.Range("B1")).Range("A1")
    '----------------------------------------
    'STORE CURRENT SELECTD CELL' WORKSHEET INDEX AND ADDRESS
        .Range("A1") = Target.Address
        .Range("B1") = Worksheets(Target.Parent.Name).Index
        On Error Resume Next
        Application.EnableEvents = False
    '----------------------------------------
    'FIND DEPENDENTS AND GET CELL ADDRESS (rng)
            oldcell.ShowDependents
            Application.Goto Reference:="R1C1"
            ActiveCell.NavigateArrow TowardPrecedent:=False, ArrowNumber:=1, _
                LinkNumber:=1
            Set rng = Worksheets(ActiveCell.Parent.Name).Range(ActiveCell.Address)
    '----------------------------------------
    'CLEAR TRACER ARROWS AND ASSIGN MATCHING COLOR TO DEPENDENT
            Worksheets(1).Activate
            ActiveSheet.ClearArrows
            Range(.Range("A1")).Select
            rng.Interior.Color = oldcell.Interior.Color
        Application.EnableEvents = True
        On Error GoTo 0
    End With
    '----------------------------------------
    'CLEANUP
    Set rng = Nothing
    Set oldcell = Nothing
    Application.ScreenUpdating = True
    End Sub
    Note: This only works for one dependent on the same or different sheet as the referenced cell. The code can be adjusted for more that one dependent but that will be another rainy day! I hope someone can benefit from this.

    HTH,
    Maud

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Quote Originally Posted by Maudibe View Post
    Since there are no Excel functions to return a cell’s color nor conditional formatting to detect changes in a cell’s color, we must look to VBA for the answer. Unfortunately, none of the VBA events detect a cell color change either. Seems like Microsoft dropped the ball on this one.
    I feel compelled to reply as this is one of my pet peeves. I have to respectfully but completely disagree. This is not MS dropping the ball (Lord knows they have in plenty of areas, and even deflated the ball a little in others). Colours are not data and should not be used as such. They should illuminate data, in which case any colour that applies on one cell can be applied to other cells using the same logic based on the same data.

    If for some reason you just need to display a range on more than one sheet, you can use a linked picture.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    any colour that applies on one cell can be applied to other cells using the same logic based on the same data.
    Rory,

    Thank you for your opinion. Even if users may be using colors beyond their intent as you suggest, at the end of the day, there is still not a built-in way for Excel to detect a cell's color change. And while there are times when a change in color is based on logic, there are also times when there is no logic as when a user manually changes a cell color. The intent here was to find an event raising solution when no logic exists. Moving beyond merely changing of cell's colors, users are also looking for color a change to trigger other events such as launching code or a message box.

    Since it conception, power users have been searching for ways to expand and test Excel's limits. Triggering off of a color change is not a new ponder. I just thought that Microsoft would have addressed it by this point.

    Maud

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    My point was that I do not think this is an error on Microsoft's part. I don't think they should waste time on encouraging bad practices when they have so many other things to concentrate their resources on. Not that they always concentrate those resources where they should, of course.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Rory

    Colours are not data and should not be used as such.
    This is a grey area and it seems it makes you see red.
    ..at least I hope you do if you are approaching traffic lights.
    Speaking as a former Astrophysicist, when it comes to spectral analysis Colours are definitely data.

    zeddy

Posting Permissions

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