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