PDA

View Full Version : Solved: Change color figures, with discretion



marreco
10-11-2012, 05:50 PM
Hi.
I need help, in two parts of my code.
When the cell 'A2' equals 'ret' form does not change color
When the cells 'A2' is equal to or different from empty ("Los Tri, Los") all forms must be white.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A2")) Is Nothing Then
If Target.Value = "Ret" Then 'that part is not working
ActiveSheet.Shapes("Ret").Select 'that part is not working
ActiveSheet.Shapes("Ret").Fill.BackColor.RGB = vbYellow 'that part is not working

ElseIf Target.Value = "Tri" Then
ActiveSheet.Shapes("Tri").Select
ActiveSheet.Shapes("Tri").Fill.ForeColor.RGB = vbRed

ElseIf Target.Value = "Los" Then
ActiveSheet.Shapes("Los").Select
ActiveSheet.Shapes("Los").Fill.ForeColor.RGB = vbGreen
Else
ActiveSheet.Shapes("Los, Tri, Los").Fill.ForeColor.RGB = vbWhite 'that part is not working

End If
End If
End Sub

Simon Lloyd
10-11-2012, 08:28 PM
Got a sample workbook?

Teeroy
10-12-2012, 04:28 AM
The following works for me (in the sheet container of course). In this case I find a CASE SELECT more understandable code.

ps: changed backcolor to forecolor for "Rec"

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A2")) Is Nothing Then
Select Case Target.Value
Case "Ret"
ActiveSheet.Shapes("Ret").Fill.ForeColor.RGB = vbYellow

Case "Tri"
ActiveSheet.Shapes("Tri").Fill.ForeColor.RGB = vbRed

Case "Los"
ActiveSheet.Shapes("Los").Fill.ForeColor.RGB = vbGreen
Case Else
ActiveSheet.Shapes("Ret").Fill.ForeColor.RGB = vbWhite
ActiveSheet.Shapes("Tri").Fill.ForeColor.RGB = vbWhite
ActiveSheet.Shapes("Los").Fill.ForeColor.RGB = vbWhite
End Select
End If
End Sub

marreco
10-12-2012, 04:34 AM
that's right!!


Very many thanks!!