Excel VBA - Reference chart data point by name instead of index number -

I have a basic gantt chart with a table in the form of a database, however, when there is no other name on the sheet Is chosen,

The same data is identical for each name and I want its bar to get another color.

I know of this way to reference a data point:

  ActiveChart.FullSeriesCollection (2). Digit (3)  

But this will not work in my example because the amount of data points keeps changing and the same thing is not always in the third position.

I tried to do this, but as I thought I would throw a kind of mismatched error:

  ActiveChart.FullSeriesCollection (2) Points ("Some String" )  

Is it possible to refer to the data point in VBA by its name?

with a simple gantt chart that looks like this:

You can change the color of the first b, return the first XValue Match the name with your name and then set the corresponding color of the color:

  Options clear sub-changepointbcolor () Dimensional dimensions in the form of variable x dim varValues Cht as the chart set cht = worksheet ("sheet 1"). ("Chart 3"). Cht.SeriesCollection with chart (2) varValues ​​=. XValues ​​X = UBound for VarValues ​​(varValues) if varValues ​​(x) = "B" then Point (x). External Color = RGB (140, 125, 230) End if the next X end  

result