Changing the font colour in a shape

Question

I am trying to change the font colour in a shape depending on the value of a cell on a different sheet and keep getting

Run-time error '1004':

Private Sub Colours()
    Dim firstSheet As Worksheet, secondSheet As Worksheet

    Set firstSheet = Sheets("Stats - Overall")
    Set secondSheet = Sheets("Dashboard")

    If firstSheet.Range("I41") > 0 Then
     secondSheet.Shapes("Freeform: Shape 19").TextFrame.Characters.Font.Color = RGB(69, 255, 69)
    ElseIf firstSheet.Range("I41") < 0 Then
     secondSheet.Shapes("Freeform: Shape 19").TextFrame.Characters.Font.Color = RGB(255, 0, 0)
    Else
     secondSheet.Shapes("Freeform: Shape 19").TextFrame.Characters.Font.Color = RGB(250, 200, 15)
    End If
End Sub

I cannot figure our what is wrong.

is `firstSheet` is VBA code name of the Worksheet? if not try `Worksheets("firstSheet")` and so on. Also confirm existence of `Freeform: Shape 19' etc — Ahmed AU, Feb 12 '19 at 12:10
Which line of code is causing the error? What's the text of the error? — Cindy Meister, Feb 12 '19 at 12:20
@ahmed The first sheet variable is one I set up earlier. Set firstSheet = Sheets("Stats - Overall") Set secondSheet = Sheets("Dashboard") The shape exists but the error is on the secondSheet.shapes on the the second line — Eyitayo Lawal, Feb 12 '19 at 13:31
I checked with your code in Excel 2007 and find Shape Name with **:** (Colon) is not accepting as pointed out by @Vityata. Making shape name `Freeform: Shape 19` and removing **:** after `else:` made your code work. — Ahmed AU, Feb 12 '19 at 16:19
I changed it to the above and still no luck. I even tried Vityata's code in place of mine and got a different error, a compile error. The new error highlighted the "shapes" part of line 4 of Vityata's code. — Eyitayo Lawal, Feb 13 '19 at 09:38

Vityata · Answer 1 · 2019-02-12T12:18:22.457

First thing first - Remove the : after the Else. This is not Python and the : probably will fail your code somewhere some day. E.g. here:

Sub TestMe()
    If False Then:
       Debug.Print "OK"
End Sub

Then, there could be plenty of reasons for the 1004 error. Usually it is a locked worksheet with locked shapes. Or something similar. In general, build something like this, which is working in a worksheet and try to work from there:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub

    With Shapes(1).TextFrame.Characters.Font
        If Range("A1") > 0 Then
            .Color = RGB(0, 0, 0)
        Else
            .Color = RGB(200, 200, 200)
        End If
    End With
End Sub

Changing the font colour in a shape

1 Answers1