I am trying to change the colors of some shapes based on a cell value like the code below
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("$O$11").Value = "X" Then
Worksheets("Print").Shapes("Oval1").Fill.ForeColor.SchemeColor = 2
Else
Worksheets("Print").Shapes("Oval1").Fill.ForeColor.SchemeColor = 3
End If
End Sub
I have an unknown number of shapes as well as an unknown number of sheets. One document may have thirty shapes, while the next may have two hundred shapes.
Each shape is numbered such as Oval1, Oval2, Oval3, etc. In Column A are numbers that refer to the shapes. So if cell A11 contains the number “1”, it refers to the shape “Oval1”. If cell A13 contains the number “2”, it refers to the shape “Oval2”.
In Column O of the same row is the cell that I am checking for the “X” that determines the color of the cell.
While the numbers are consecutive, the rows are sometimes skipped. So Cell A13 may contain the value “2” (which refers to “Oval2” and I am checking Column O for the “X” in row 13). Cell A20 may contain the value 3 (which refers to “Oval3” and I am checking Column O for the “X” in row 20).
Because it skips, I never know for sure what sheet contains the data for the specific shape. I need something that can check column O for the letter “X” and change the corresponding shapes color for each sheet. All the shapes are located on the sheet named "Print"
Any help would be appreciated.
Thanks
Bookmarks