Format cell based on data in 2 other cells

I am trying to write a VBA macro which colours one cell (E) based on the data in two other cells (A) and (C). The macro keep falling over on the Set wsData line, generating an RTE 9 - subscript out of range error

Here is the code

Sub LDT1_HOME_GREEN()

    Dim wsData As Worksheet

    Dim rAnchorCell As Range
     
    Dim iDataRowsCount As Long
    
    Dim iDataRow As Long
    
    Dim asLeagues() As String
    
    Dim iLeaguesCount As Long
    
    Dim iLeague As Long

    Set wsData = thisWorkbook.Worksheets("Lay The Draw") 

    Set rAnchorCell = wsData.Range("A1") '<= this is where data starts
    
    iDataRowsCount = rAnchorCell.Offset(100000).End(xlUp).Row - rAnchorCell.Row + 1
    
    iLeaguesCount = 25 
    
    ReDim asLeagues(iLeaguesCount)
    
    asLeagues(1) = "Austria: 2. Liga"
    asLeagues(2) = "Australia: A-League"
    asLeagues(3) = "Bulgaria: Parva Liga"
    asLeagues(4) = "Czech Republic: Czech Liga"
    asLeagues(5) = "Denmark: Superliga"
    asLeagues(6) = "Germany: Bundesliga II"
    asLeagues(7) = "Greece: Superleague"
    asLeagues(8) = "Hungary: NB I"
    asLeagues(9) = "Portugal: Primeira Liga"
    asLeagues(10) = "Portugal: Segunda Liga"
    asLeagues(11) = "Poland: Ekstraklasa"
    asLeagues(12) = "Qatar: Q League"
    asLeagues(13) = "Turkey: Super Lig"
    asLeagues(14) = "Chile: Primera Division"
    asLeagues(15) = "Colombia: Primera A"
    asLeagues(16) = "Ecuador: Primera B"
    asLeagues(17) = "Iceland: Inkasso-Deildin"
    asLeagues(18) = "Ireland: Premier Division"
    asLeagues(19) = "Korea: K-League Classic"
    asLeagues(20) = "Lithuania: A Lyga"
    asLeagues(21) = "Norway: Elieserien"
    asLeagues(22) = "Peru: Segunda Division"
    asLeagues(23) = "Sweden: Superettan"
    asLeagues(24) = "Sweden: Division 1 - Sodra"
    asLeagues(25) = "USA: MLS"
    

    For iDataRow = 1 To iDataRowsCount
    
        If rAnchorCell.Cells(iDataRow) Like "LTD1_HOME*" _
         Then
    
            For iLeague = 1 To iLeaguesCount
            
                If rAnchorCell.Cells(iDataRow, 3) Like "*" & asLeagues(iLeague) & "*" _
                 Then
                 
                    With rAnchorCell.Cells(iDataRow, 5).Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        
                        .Color = 5230738
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
                
                End If
                
            Next iLeague
        
        End If
    
    Next iDataRow

End Sub

Any help gladly accepted

Subscript out of range means the sheet/tab name is not found. Check the tab name is spelt exactly the same, and for leading or trailing spaces.

Or, you could double click on the tab name, copy it, and paste it into your code.

Originally Posted by TMS

Subscript out of range means the sheet/tab name is not found. Check the tab name is spelt exactly the same, and for leading or trailing spaces.

Or, you could double click on the tab name, copy it, and paste it into your code.

Cheers and thanks for your reply TMS

No, the sheet is correctly named, no spaces or anything else, but maybe the workbook needs to be defined. it is a workbook with 10 sheets in it. The workbook's name is Predictology-Reports Football Advisor.xlxs I mean, it should work with thisWorkbook.Worksheets etc, as it is the only workbook open and the sheet I am in is Lay The Draw, but maybe it needs

Dim wb As Workbook: Set wb = thisWorkbook

I am unsure, as much of the code was not compiled by me.

I tried adding that line to the beginning of the code, but it still fell over on the same line

So I'm kind of stuck

Oh, forgot to add this is in Excel for MacOS. Not sure if that makes a difference.

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

I have added a sample file to the original post. It has the rows just as in the original sheet.

The idea behind it is that if column A has LTD1_HOME or LTD1_HOME_SUMMER in (that is why it lists LTD1_HOME*) and column C has any of the leagues showing in the original post, the the corresponding cell in E will turn green

I hope that helps

Cheers

Are we looking at the correct file? There isn't any error on my screen.

In your example, (Norway: Eliteserien) from column C won't highlight in column E. Is there typo error with extra T or it's correct?

I've added your code into the sample file and removed some blank lines (just for my benefit).

It runs through and colours cells, although I don't know if they are correct.

All looks good to me.

You're welcome. Thanks for the rep.

Try changing this:

     Set wsData = thisWorkbook.Worksheets("Lay The Draw")

to this:

     Set wsData = ActiveWorkbook.Worksheets("Lay The Draw")