Macro to change an object's color

[turner38]

I have several charts that contain an oval on each of them. My plan is to use them as an quick indicator of performace for each chart. I am needing a macro that will change the color of the each oval to either red, yellow, or green depending on what is displayed in a cell. (i.e. if the word "yellow" is in the cell the the object's color would be yellow, if the word is "green" then the object's color would be green, if the word is "red" then the object's color would be red.)

Any advice would be greatly appreciated!

John

 

[Jared_8878]

There is probably a faster way,

This works as just a macro

Sub testo()

If Range("A1").Value = "Gray" Then
ActiveSheet.Shapes.Range(Array(1, 2)).Fill.ForeColor.RGB = RGB(122, 122, 122)
End If

If Range("A1").Value = "Green" Then
ActiveSheet.Shapes.Range(Array(1, 2)).Fill.ForeColor.RGB = RGB(122, 255, 122)
End If
End Sub



with the cell A1 containing the word you want to change

in the code adjust the .value = "color you want in cell" and the RGB values to match the color.




I am sure it would be most convenient to put in a worksheet change event so when you change the cell A1 it will trigger the macro.

 

[Jared_8878]

ok, in A1 do a validation list with your color names

then open up the macro window and double click on the sheet you are working in.

On the window that just popped up paste in the following:


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Range("A1")

If Target.Value = "Gray" Then
ActiveSheet.Shapes.Range(Array(1, 2)).Fill.ForeColor.RGB = RGB(122, 122, 122)
End If

If Target.Value = "Green" Then
ActiveSheet.Shapes.Range(Array(1, 2)).Fill.ForeColor.RGB = RGB(122, 255, 122)
End If
End Sub




add in the if....end if's to suit how many color changes you need. (don't forget to make the rgb changes and target.value changes to match the color sets you have in your validation list. Also, I think you said you had three ovals if that is all you have then change the array(1,2) to array(1,2,3).

 

[turner38]

Thanks Jared. I should have probably clarified early. For the cells that I am referencing with the text red, yellow, green, I have multiple cells that do this. Each cell corresponds to a chart containing the oval oject. The cells are (AK11:AK24). These cells have formulas in them that produce the word based on the metric performace. So for example cell AK11 could have the word green so for chart 1 the oval would need to be green where as cell AK12 would have the word yellow and the chart 2 oval would need to be yellow. Does this change the macro logic?

Thanks for your help!

John

 

[Jared_8878]

yes, so AK:11 to AK24
you would have 14 of those that I posted, but changing the target cell to match each of your AK11 to AK24 cells.

 

[Jared_8878]

I do not know of a way to easily detect the names other than systematically,

so I would just insert a regular macro:

Sub atomic()

ActiveSheet.Shapes(2).Fill.ForeColor.RGB = RGB(1, 255, 1)
End Sub

and run it - this will turn whichever oval is Shapes(2) into a bright ugly green, so then you will know which oval is for what chart so you can assign them properly in the a-fore mentioned macros. (then increment through Shapes(2).. Shapes(3)... to find the other assignments.

 

[turner38]

I am getting a run-time error 438: Object does not support this property or method when I try to run the macro.

I found a way to identify the Oval numbers by selecting the shape and looking at the top left of my screen right next to my formula bar it tells me which number it is. My ovals are in my chart meaning I can't drag them out of the chart area. Do I need to reference the chart they are in as well?

 

[Jared_8878]

I believe I have simplified it a bit now.

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Range("AK11")

If Target.Value = "Gray" Then
Shapes("Oval 11").Fill.ForeColor.RGB = RGB(122, 122, 122)
End If

If Target.Value = "Green" Then
Shapes("Oval 11").Fill.ForeColor.RGB = RGB(122, 255, 122)
End If
End Sub

Then just modify your set target - range("bla bla")
your shape names
and colors to suit what you want.

 

[Jared_8878]

again, this is to be pasted not in a module but by double clicking on the sheet in your vbaproject in the visual basic editor.

 

[turner38]

It is still saying it can't find the object. I think it is because my shape is in a Chart. Any ideas why it is not finding the shape?