colour macro

[Guest]

I wonder if some can help\suggest, I'm creating a report and would like to
add shape to represent status. Is it possible to automate the shape to shorw
red, amber or green by clicking on it? Can I assign a dropdown to a shape or
do I need to write a macro and assign it to a shape? Please advise.
Afia

 

[Norman Jones]

Hi Alfa,

Try assignin the shapes to the following macro:

'=============>>
Public Sub Tester()
Dim SHP As Shape

Set SHP = ActiveSheet.Shapes(Application.Caller)

With SHP.Fill.ForeColor
Select Case .RGB
Case vbGreen: .RGB = vbYellow
Case vbYellow: .RGB = vbRed
Case Else: .RGB = vbGreen
End Select
End With
End Sub
'<<=============

Succesive clicks will cycle the shape's colour through
green, amber and red;

 

[Guest]

Thank you, it works and brilliant!!!
Can I make the shape to automate its fore colour as in the cell?
Say c4:w4 got conditional formatting for Red, amber and green and if I add
shape to each cell, can it be automated as the cell text?

 

[Norman Jones]

Hi Afia,


Perhaps youcould utilise the shape's TopLeftCell property
to identify the cell of interest and you could use the same
conditions as used for the conditional formatting to determine
the shape's colour.

Perhaps, for example, try something like:

'=============>>
Public Sub Tester()
Dim SHP As Shape

Set SHP = ActiveSheet.Shapes(Application.Caller)

With SHP.Fill.ForeColor
Select Case SHP.TopLeftCell.Value
Case Is <= 10: .RGB = vbYellow
Case Is <= 50: .RGB = vbRed
Case Is <= 100: .RGB = vbGreen
Case Else: .RGB = vbMagenta
End Select
End With
End Sub
'<<=============