Shape formatting

[Corleone]

Software: Excel365

Hi im using the following code to try and get a shape to change colour based on the text in Cell "C8"
The code is contained in the worksheet tab

however, when testing it out by typing in either Red, green or yellow i get an error message - "wrong number of assignments or invalid property assignment"

Private Sub WORKSHEET_CHANGE(ByVal TARGET As Range)
If Range("C8") = "RED" Then
ActiveSheet.Shapes.Range.Fill(Array("RUGELEY")).Select
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0)

Else

If Range("C8") = "GREEN" Then
ActiveSheet.Shapes.Range.Fill(Array("RUGELEY")).Select
Selection.ShapeRange.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent6

Else

If Range("C8") = "YELLOW" Then
ActiveSheet.Shapes.Range.Fill(Array("RUGELEY")).Select
Selection.ShapeRange.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent4

End If
End If
End If

ActiveSheet.Cells(8, 3).Select

End Sub


Thanks

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Range("C8") = "RED" Then
      ActiveSheet.Shapes("RUGELEY").Fill.ForeColor.RGB = RGB(255, 0, 0)
   
   ElseIf Range("C8") = "GREEN" Then
      ActiveSheet.Shapes("RUGELEY").Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent6
   
   ElseIf Range("C8") = "YELLOW" Then
      ActiveSheet.Shapes("RUGELEY").Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent4
   
   End If
   
   ActiveSheet.Cells(8, 3).Select
End Sub

 

[Corleone]

Perfect!
Just out of interest would you know why my previous code wasn't working?

Thanks again for your help!

 

[Fluff]

Because the syntax is all wrong.

Another option that will only run when you change C8 is

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "C8" Then
      Select Case Target.Value
         Case "RED"
            Me.Shapes("RUGELEY").Fill.ForeColor.RGB = RGB(255, 0, 0)
         Case "GREEN"
            Me.Shapes("RUGELEY").Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent6
         Case "YELLOW"
            Me.Shapes("RUGELEY").Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent4
      End Select
   End If
   Me.Cells(8, 3).Select
End Sub

 

[Corleone]

OK Cheers, your help is appreciated

My next step is to be able to do this for multiple shapes on the same sheet - end endgame is to have a number of shapes on a map which change colour based on dates
changing in the source data - so it will be roughly 30 or 40 different shapes representing geographical locations of projects in the uk.

i just tried adding to the existing code to test it on a 2nd shape based on the text in cell C9 but nothing happens, does the "active sheet cells(8.3).select" command need changing?

thanks again....

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("C8") = "RED" Then
ActiveSheet.Shapes("RUGELEY").Fill.ForeColor.RGB = RGB(255, 0, 0)

ElseIf Range("C8") = "GREEN" Then
ActiveSheet.Shapes("RUGELEY").Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent6

ElseIf Range("C8") = "YELLOW" Then
ActiveSheet.Shapes("RUGELEY").Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent4


ElseIf Range("C9") = "RED" Then
ActiveSheet.Shapes("MACC").Fill.ForeColor.RGB = RGB(255, 0, 0)


ElseIf Range("C9") = "GREEN" Then
ActiveSheet.Shapes("MACC").Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent6

ElseIf Range("C9") = "YELLOW" Then
ActiveSheet.Shapes("MACC").Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent4

End If

ActiveSheet.Cells(8, 3).Select
End Sub

 

[Fluff]

I see nothing wrong with that code. As VBA is case sensitive are putting RED in to the cell, rather than Red or red etc?

 

[Corleone]

Yes, im typing it in in Uppercase as per the text in the code
one thing ive noticed as that when i type the text into cell C9 it jumps back to cell C8
it doesn't return an error message either it just fails to change the shape colour.

I have named the second shape "MACC" as per the code

Cheers

 

[Fluff]

when i type the text into cell C9 it jumps back to cell C8

That's because of the last line in your code, you can just delete it.

Do you get any errors when you change C9?

 

[Corleone]

That's because of the last line in your code, you can just delete it.

Do you get any errors when you change C9?

Cheers

Ive removed the last line

The issue appears to be that it will only work on 1 shape at a time for example if i change the text in C8 now from GREEN to YELLOW it will correctly change the colour of the "RUGELEY" Shape
Ii i then try to do the same in cell C9, nothing happens

however, if i then change the text in cell C8 to something else outside of the code requirements e.g "BLUE" and then follow that by changing cell C9 from YELLOW to RED it works for the 2nd shape....
so i suspect i need to be able to change the way the code is mapped out in order for it to work on each shape individually.

Ta

 

[Corleone]

I have found a workaround by creating separate macros for each shape and then creating another macro which runs them all simultaneously
Many thanks for your help