Conditionally format a shape to glow in excel

[taylorhb]

Hi,

This is my very first post, so please forgive me for any limitations in what follows!

I'm trying to conditionally format circles in excel to glow red, amber or green based on a cell instruction stating 'RED', 'AMBER' or 'GREEN'. Can anyone give me an indication of what VBA code I would need to accomplish this?

Many thanks for your help.

H.

 

[oldbrewer]

in a new worksheet draw a circle, select a cell away from it, then turn on record macro, select the circle, colour it red, select a cell away from the circle, STOP the macro, examine the macro code

you will see that shapes are given numbers so you will need one line of code for each shape - to demonstrate your advanced skills attach the macro to one of the shapes, then clicking on that shape will colour all the shapes

 

[taylorhb]

Thanks oldbrewer.

I will do this to access the macro code for each shape. However, in terms of my ultimate aim above, do you know whether it is possible to create VBA code that will automatically change the colour of glow of the circle, based on whether a cell contains RED, AMBER or GREEN?

 

[oldbrewer]

yes - put the color index number in say cell A1 and get the macro to color the shape according to value in A1

if you put "red" in A1 the macro will have to be coded

if cells(1,1)= "red" then colornum=6 (guessing)
if cells(1,1)= "orange" then colornum=7 (guessing)
colornum = 8 (guessing)

 

[taylorhb]

Thanks again. I'm probably not explaining myself very well, but would this work with the glow function in excel as well? I've written the following, which refers to a shape 'Oval4'. What do you think?

Sub AssignGlow()

Dim shp As Shape

Set shp = ActiveSheet.Shapes("Oval4")

If (Workbooks("[Given Workbook]").Sheets("[Given Sheet]").Range("[Given Range]") = "RED") Then

shp.Glow

.Glow.Radius = 10

.Glow.Color = RGB(255, 0, 0)

Else If (Workbooks("[Given Workbook]").Sheets("[Given Sheet]").Range("[Given Range]") = "AMBER") Then

shp.Glow

.Glow.Radius = 10

.Glow.Color = RGB(249, 157, 39)

Else If ((Workbooks("[Given Workbook]").Sheets("[Given Sheet]").Range("[Given Range]") = "GREEN")

shp.Glow

.Glow.Radius = 10

.Glow.Color = RGB(141, 198, 63)



End With

End Sub

 

[oldbrewer]

ah - I have never seen the glow function (I have excel 2000)