Excel VBA change shape color based on cell value in another sheet

    Hello,


    I need help! I'm doing something and I am using shapes in Excel. I want my shape to automatically change color based on cell value in another sheet. I've used a VBA code before, however, the function only works if you are changing the value of the cell in the same worksheet. I need a code that will automatically change the color of my shape when I input a value in a cell in the other sheet.


    Hoping for your help.


    Thanks!

    Re: Excel VBA change shape color based on cell value in another sheet


    Post the code you used previously. It's a simple change to refer to a shape on another sheet when a particular cell is updated (As long as the cell is edited manually, not changing as a result of a formula recalculating).

    Re: Excel VBA change shape color based on cell value in another sheet


    Hi Cytop,


    Thank you for your response. I have used the code below. My problem is that when I changed the value of the cell, it does not automatically changed the color of the shape until I click some cells to refresh.


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Worksheet("Sheet2").Range("I9").Value = True Then
        ActiveSheet.Shapes("Square").Fill.ForeColor.RGB = RGB(247, 91, 91)
    Else
        ActiveSheet.Shapes("Square").Fill.ForeColor.RGB = RGB(43, 170, 26)
    End If
   End Sub

    Re: Excel VBA change shape color based on cell value in another sheet


    Quote

    I changed the value of the cell, it does not automatically change


    Because it is checking that Cell I9 on another worksheet is True or False - and that hasn't changed, has it?


    One question is how is I9 on Sheet2 changed? Manually or by a formula?

    Re: Excel VBA change shape color based on cell value in another sheet


    Hi Cytop,
    This is being changed manually. I also tried using the other formula before. See below. It checks the value of the cell I9 if True. The cell I9 used a formula to change. However, this does not change the color of the shape automatically. I need to double click other cell in order to refresh the sheet and to change the color of the shape.

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("I9").Value = True Then
        ActiveSheet.Shapes("Square").Fill.ForeColor.RGB = RGB(247, 91, 91)
    Else
        ActiveSheet.Shapes("Square").Fill.ForeColor.RGB = RGB(43, 170, 26)
    End If
   End Sub

    Re: Excel VBA change shape color based on cell value in another sheet


    If I9 contains a formula, what are the precedents of that formula.
    Have you tried moving that code to the Calculate event?



    Edit: I just had another thought. I really doesn't matter what color a shape is unless its sheet is the ActiveSheet.
    Might it work if you put code like that in the Activate event of sheet Square? (qualify to make sure its looking at Sheet2!F9 for the color flag)
    That would reduce the number of times that the code has to run. (since its setting a shape's color every Change event, that can add up to a lot of time.)

    Re: Excel VBA change shape color based on cell value in another sheet


    Hi Mike,


    Thank you for your response. It seems that you know a lot in VBA. However, can you give me a visual example 'coz it seems that I can't relate because I'm just a newbie in VBA. Hope you understand.

    Re: Excel VBA change shape color based on cell value in another sheet


    No I'm not sure what you mean by "visual example".


    If F9 contains a formula, the Change event isn't really the best place for your code, the Calculate event would be better.


    Also, my suggestion about putting the color code in the Activate event for the sheet with the shape... Unless there are formula dependencies that link from F9 back to the sheet with the shape, put the color testing code in the Activate event for the shape's sheet.

    Re: Excel VBA change shape color based on cell value in another sheet


    Hi Mike,


    can you suggest a code for it? For example I9 value is based on formula and I wanted to make the shape color red if the cell value is 0 and green if it is greater than 0

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login