Excel VBA Chart - Set labels of a graph to red if contains "Parenthesis"?

Question

[update]: Final code used at the end of my question.

My problem refers to a graph customization in Microsoft Excel using VBA.

I'm trying to customize the label colors of my graph to red color if the label contains a parenthesis and set to black otherwise. Find the graph below for reference (it has 4 labels). [![graph][1]][1]
_{(source: i.ibb.co)}

The desired result will look like this:

The code I've reached so far based on other examples, but without reaching my goal.

Sub Labels_graph_Test()

Dim Qrts As Series

Set ChtObj = ActiveSheet.ChartObjects("Q_Graph")
Set Qrts = ChtObj.Chart.SeriesCollection(1)

For i = 1 To 4

     With Qrts.Points(i).DataLabel

     If InStr(1, currentcell, "(*") Then With Selection.Font.Color = vbRed
     Else: Selection.Font.Color = vbBlack

     End If   

Next i

End Sub

This code is not working and is giving the "Compile Error: End if without block if".

Please don't tell me to use conditional formatting because in this situation the labels are coming from cells so the conditional formatting doesn't work.

Big thank you in advance

Let me know in case something isn't clear

Best regards, Martim Pinto

Final Code:

(for more insights, check the answer of Luuklag and FreeMan - both gave very useful insights and explanations)

Sub Labels_graph_Test()

Dim Qrts As Series

Set ChtObj = ActiveSheet.ChartObjects("Q_Graph")
Set Qrts = ChtObj.Chart.SeriesCollection(1)

    For i = 1 To 4
        With Qrts.Points(i).DataLabel
    
            If Left(.Text, 1) = "(" Then
                .Font.Color = vbRed
            Else
                .Font.Color = vbBlack
            End If
        End With
    Next i

End Sub

 [1]: https://i.stack.imgur.com/OzA82.png

Answer 1

Your formatting within the For loop makes that very difficult to read, and therefore, very easy to screw up.

If you fix up your indenting like this:

Sub Labels_graph_Test()

  Dim Qrts As Series

  Set ChtObj = ActiveSheet.ChartObjects("Q_Graph")
  Set Qrts = ChtObj.Chart.SeriesCollection(1)

  For i = 1 To 4

    With Qrts.Points(i).DataLabel
      If InStr(1, currentcell, "(*") Then
        With Selection.Font.Color = vbRed
      Else
        Selection.Font.Color = vbBlack
      End If

  Next i

End Sub

It's really easy to see that you have two With blocks that aren't closed out with an End With.

Also, you're not using With correctly. I believe (off the top of my head, don't quote me on this, you may need to do a bit of additional research). that it should look something like this:

    With Qrts.Points(i).DataLabel
      If InStr(1, currentcell, "(*") Then
        .Font.Color = vbRed
      Else
        .Font.Color = vbBlack
      End If
    End With

Note that the statement within the If and Else statements begin with a period (.). That tells the compiler to "start looking for this shortened function/method/property at the end of the object that I called out in that With statement back there". i.e. it will translate .Font.Color to Qtr.Points(i).DataLabel.Font.Color which is what you want to set to vbBlack or vbRed.

Again, I'm not 100% certain that's the exact location to go to in order to set the colors - you should be able to find that one fairly easily here on SO, in the MS Docs, or by using the macro recorder to set the color by hand and let it tell you the exact property to set. (Macro Recorder is excellent as the lazy man's way of finding those properties. It sucks at writing nice, readable, efficient, maintainable code.)

currentcell: my initial assumption was that this was a variable global to the module or the entire project that contained the text of whatever you wanted to look at. Seems I was wrong (that's what happens when you assume)...

I believe that what you want to replace currentcell with would be Qrts.Points(i).DataLabel.Text so you can look at the actual label for this particular point. However, you have that With statement in there, so you can shorten that to .Text. Your If statement would look like this:

If Left$(.Text, 1) = "(" Then

see "Another thought" section for full details on the rest of the rewrite of this If statement.

Again, I'm not 100% certain that .Text is the exact property, but, off the top of my head, I believe that's it. If not, in the Immediate Window of the VBE, you can type ChartObjects.Points(i).DataLabel. and the IntelliSense should give you the list of all possible options. Scroll through the list looking for likely candidates. If you're still not sure, do that while the code is running (step through to the If statement, then do the above process). If you proceed that with a ? in the Immediate Window, it will print the value of whatever you're asking for so you can examine it. Note - this is just one of a multitude of ways of figuring this out (MS Docs, SO, Google, etc. are other options).

Another thought: Instead of using the somewhat less readable InStr function to check to see if the first character of the string is a left-paren, you can simple do:

If Left$(currentcell, 1) = "(" Then

I'm not certain about the performance level difference, and it's probably pretty minute, but the mental energy required of the next person to have to maintain this code (potentially future you), will be much lower, thus more maintainable code.

NB: Left and Left$ are nearly identical. However Left returns a variant that needs to be implicitly cast to a string for the comparison to "(", while Left$ returns a string that doesn't need the implicit cast. It goes a long way toward code that "Does what it says and says what it does".

Answer 2

There are two three problems: (as per comments)

1. As you have text after your Then on the same line your If statement is considered as a one-liner, and that doesn't need an End If. Hence there is an End If without an corresponding If in your code. See for examples the Microsoft docs

2. There is a With that doesn't have a corresponding End With.

3. Your currentcell is not declared and thus holds no value. You are looking for the text of the datalabel. As you are already inside a With loop for your Datalabel you can replace currentcell with .Text to reference the text value of the current Datalabel.

Your code should then look something like: (Untested)

For i = 1 To 4

     With Qrts.Points(i).DataLabel

         If InStr(1, currentcell, "(*") Then 
             .Font.Color = vbRed
         Else 
             .Font.Color = vbBlack
         End If   
     End With
Next i