Check to Table columns for Criteria VBA

[Skybluekid]

Hi

I have two columns in a table, Column V and Column W. I would like to check the cells in the row of each column to see if the conditional formatting is Red. If so, then bring back the row number.

I am using the ListColumns to get the Header name as the table will expand over time.

Thanks in Advance.

 

[JEC]

For example:

Sub jec()
 Dim it, a
 For Each it In Range("V:W").SpecialCells(2)
   If it.DisplayFormat.Interior.Color = vbRed Then a = a & it.Row & vbLf
 Next
 MsgBox "Rows:" & vbLf & a
End Sub

 

[Skybluekid]

Thanks @JEC ,

Sorry didn't make myself clear. In my table, I would like to reference the Column names, which I have called "Col V' and "Col W", in the Header Row. This is because the table will expand in the future.

 

[JEC]

Ok, this is how you refer to Listobject columns.

Sub jec()
 Dim it, a
 With Sheets(1).ListObjects(1)
   For Each it In Union(.ListColumns("COL V").Range, .ListColumns("COL W").Range)
     If it.DisplayFormat.Interior.Color = vbRed Then a = a & it.Row - .HeaderRowRange.Row & vbLf
   Next
   MsgBox "ListObjectRows:" & vbLf & a
 End With
End Sub

 

[Skybluekid]

Thank you @JEC it looks good.