Matching 'Chart Series title' to equation driven cells to set the series line colour

I am struggling to get the below code to find a match between the Chart Series name & a selected Range cell text, but only when both are driven by equations. If no equations are used (and text only is used in the colour cells) it works fine. Any ideas why this may be the case?

Sub ColorBySeriesName()
  Dim rPatterns As Range
  Dim iSeries As Long
  Dim rSeries As Range
  
  
  Set rPatterns = Worksheets("Chart").ListObjects("ColorBySeriesName").DataBodyRange
  
  With ActiveSheet.ChartObjects("Chart1").Chart
    For iSeries = 1 To .SeriesCollection.Count
      Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name, _
          LookAt:=xlWhole)
      If Not rSeries Is Nothing Then
        .SeriesCollection(iSeries).Format.Fill.ForeColor.RGB = _
            rSeries.Interior.Color
        .SeriesCollection(iSeries).Format.Line.ForeColor.RGB = _
            rSeries.Interior.Color
      End If
    Next
  End With
End Sub

Also attaching a simplified worksheet to illustrate the problem. If you click the 'Refresh' button above the chart on the second worksheet then nothing happens. But replace the equations in the Colour table with plain text (A, B, C etc...) then hit refresh and all will work just fine!! Thanks for any help!

include the LookIn parameter

      Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name, _
          LookAt:=xlWhole, LookIn:=xlValues)

Andy I could also do with modifying the code so that instead of looking at the same cell (the one probed for the text match) for the associated fill colour, it would instead look at the neighbouring cell.

I.e coloured cells all in table column 1, and the text to match against in table column 2. Presumably I will need to use an Offset somewhere?? But have tried and failed so far.... Is there a simple fix do you know?

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

If you need more help with this, you should remove the SOLVED tag.

Originally Posted by AliGW

If you need more help with this, you should remove the SOLVED tag.

I have switched back 'Answered' as have submitted the second query as a new thread. Thanks