Conditional formatting shapes based on the fill color of another cell

[AndersNOR9]

Hi, I am making a balanced scorecard in excel. I have on sheet called "main", where I have a figure with different goals. To the left of each goal I have a shape which sends me to the sheet where the data for that goal is placed when clicked on. In each sheet with data, the data is filled with either green or red. This is based on whether the number is over or under the goal. I would like to be able to make the shape not only get linked to the cell where the data is located, but also get filled with the same color as the reference cell.

Is this possible?
Best regards, Anders.

 

[CrispyAsian]

Could you show an example of the what the sheets you're working with look like?

 

[AndersNOR9]

Hi, have to be a little careful since its for my job (Currently doing an internship).

First picture: This is the sheet "main" with a figure. Both of the green boxes are linked to the sheet "SCM".

Second picture: I removed all data, but inserted 6% as an example. The cell is formatted with conditional formatting so its filled with red
when > 5 % and green when < 5%. I want the green shape on sheet "main" to have either green og red fill depending on whether the cell with 6% is filled with green or red.

 

[CrispyAsian]

Okay I think I get what you want. Could you post the code you're using to link the green box to the sheet? I need to know the shape name and cell address of the reference cell to make it work.

 

[AndersNOR9]

I'm actually just using a hyperlink in the shape. Is that an issue?

Thanks.

 

[CrispyAsian]

No not at all. I'm just trying to take the guess work out it for you. Basically what you're looking for is this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
    With Sheets("main").Shapes("Rectangle 1")
        If Sheets("SCM").Cells(1, 1).Value < 0.05 Then
            .Fill.ForeColor.RGB = RGB(102, 204, 0)
        Else
            .Fill.ForeColor.RGB = RGB(204, 0, 0)
        End If
    End With
    End If
End Sub

Whatever sheet this code is added to will automatically run when a certain cell is changed. In this case cell "A1". So if you pasted this on Sheet "SCM" then every time you change cell "A1" if it less than 5% it will make the rectangle "Rectangle 1" on the main sheet red, if it more it will make it green. Since you have more than one box linked to different cells on the same sheet, you can use an ElseIf or Case arguement to make both update automatically. But in order to modify the code to do that I need to know what the name of the shapes you're using are called and what the cell address of the cells they are referencing are.

 

[AndersNOR9]

Hi all the shapes are Rectangle

First: file:///Users/Anderstrond/Downloads/Scoreboard template FINAL.xlsx - SCM!F9
Second: file:///Users/Anderstrond/Downloads/Scoreboard template FINAL.xlsx - SCM!F11

Thanks.

 

[CrispyAsian]

I'm guessing you were trying to show me the cell addresses? I'm just going to assume it's "F9" and "F11". So now the code will look like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$F$9" Then
    With Sheets("main").Shapes("Rectangle 1")
        If Sheets("SCM").Cells(9, 6).Value < 0.05 Then
            .Fill.ForeColor.RGB = RGB(102, 204, 0)
        Else
            .Fill.ForeColor.RGB = RGB(204, 0, 0)
        End If
    End With
    ElseIf Target.Address = "$F$11" Then
    With Sheets("main").Shapes("Rectangle 2")
        If Sheets("SCM").Cells(11, 6).Value < 0.05 Then
            .Fill.ForeColor.RGB = RGB(102, 204, 0)
        Else
            .Fill.ForeColor.RGB = RGB(204, 0, 0)
        End If
    End With
    End If
End Sub

However this won't work for you until you find out what Rectangle # you need to use. Each rectangle will have a specific number based on what order you added it to the sheet. If you right click on one of the rectangles and look next to the formula bar, it should say "Rectangle 1" or whatever that rectangle's number happens to be. You need to replace the "Rectangle 1" and "Rectangle 2" in my code with whatever those rectangle numbers are on "main". Your top rectangle should replace "Rectangle 1" and your bottom rectangle should replace "Rectangle 2". Then if you add this code to the "SCM" sheet everytime you change the number in "F9" or "F11" it will change the rectangle's color based on if it less or greater than 5%.

 

[AndersNOR9]

Hi CrispyAsian,

Thanks! Works great. A couple of questions:
1. When i fill in a new value at F9, ctrl + z to go back doesn't work. Is this a side effect of the code?
2. Instead of having < 0.05, could I just reference the cell where I have the value 5%? If the management for example decides to change the goal from < 5% to < 3% they would need me to go into the code and change the value for the boxes on main to work properly.
3. What does the Cells(9.6) in the code mean? (If Sheets("SCM").Cells(9, 6).Value < 0.05 Then)

 

[CrispyAsian]

Hey, glad to hear it's working for you. To answer your questions:

1. Never heard of that happening before. Never seemed to happen to me when I tested it. I look into you and let you know if I find anything.

2. So I'm not a hundred percent what you're asking because the box is referencing the value of the cell. The macro can't determine what you have the conditional formatting set to, only what the cell value is. I might be able to set it so that the box turns red if the cell is red due to the conditional formatting. Let me know if you need it to be that way.

3. Cells(9, 6) is calling the a cell based off of an x-/y-axis. So x-axis 6 is going 6 places to the right which gives you "F" and 9 on the y-axis is 9 places down so row 9. It's just the way the system is checking the value of cell "F9".

If you have an other questions or problems please let me know.