Solved: Shape colour

[slamet Harto]

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]

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]

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]

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]

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]

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]

Hi Bob
You Rock !!! my masterpiece

Thank you so much
Best Regards,
Harto