PDA

View Full Version : Solved: Shape colour



slamet Harto
01-09-2009, 08:13 PM
Hi there,

Hope you are doing good.

I have a question for format each shape based on the contents of columns E.

If value in Column E < 70% then shape colour would be red
If E value between 70% to 80% then shape colour would be orange.
If E value between 81% to 95% then shape colour would be Yellow
otherwise Green colour.

Please find the attached sample workbook.
Thank you for assistance.
Rgds, Harto

Bob Phillips
01-10-2009, 04:30 AM
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "E7:E14"
Dim nCI As Long

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

Select Case True

Case .Value <= 0.7: nCI = 10
Case .Value <= 0.8: nCI = 53
Case .Value <= 0.95: nCI = 13
Case Else: nCI = 17
End Select

Shapes(.Offset(0, -3).Value).Fill.Solid
Shapes(.Offset(0, -3).Value).Fill.ForeColor.SchemeColor = nCI
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

slamet Harto
01-10-2009, 06:31 AM
Hi Bob,

Glad to see you again and thanks for quick reply.
However, it didn't work.

Please let me know if you need further details.
Regards,
Harto

Bob Phillips
01-10-2009, 07:59 AM
Hi harto,

I forgot to mention that I renamed the shapes to coincide with the text, so 1A, 1B, 1C, 1D, 2A, etc.

slamet Harto
01-11-2009, 03:32 AM
Hi Bob,

How can I rename it? or can you post the workbook for me, please?
Thank you for assistance and highly appreciated it.

Rgds, Harto

Bob Phillips
01-11-2009, 04:31 AM
Harto,

All you need to do is select the shape and the name pops up in the Names box to the left of the formula bar. Change it there.

slamet Harto
01-11-2009, 05:01 AM
Hi Bob
You Rock !!! my masterpiece

Thank you so much
Best Regards,
Harto