Intersecting range and array

[mshbhwn98]

I have a dynamic range called rngProcs. This range is 3 columns wide. Defined as =OFFSET(Mod!$B$9:$B$1000,0,0,COUNTA(Mod!$B$9:$B$1000),3). The first column as this range (B) are numbers. i.e 1,2,3,4 going down from B9.
I also create sheets with numbers on them. Let's say 2,5,6. I want to highlight any row in this range where the sheet name matches the first column. I'm a bit stuck on how to do this. Any help would be appreciated.

Kind regards

[snb]

=countif(Mod!B9:B1000,A1)>0

[Yongle]

Run this from the sheet which contains range "rngprocs"

This is not dynamic, and if worksheet values will change, then need to use conditional formatting

What code does
For each worksheet in the workbook it compares the value of each number in columnB (converted to text) with the worksheet name and turns the backgound colour of the cell in columnB grey if they are equal. Checking starts in cell B9.

Sub sheetnameMatch()
Dim CellValue As String 'to hold cell value as text
For i = 1 To Sheets.Count
    For r = 9 To 8 + Range("rngprocs").Rows.Count
        CellValue = ActiveSheet.Cells(r, 2).Value
            If CellValue = Sheets(i).Name Then
                ActiveSheet.Cells(r, 2).Interior.ColorIndex = 15
            End If
    Next r
Next i
End Sub

[mshbhwn98]

Thank you so much that's great