Highlighting intersecting values in two tables

**wilro85** · 08-22-2007, 02:20 PM

I have two tables that have some intersecting values. Table one is several times longer than table two. The columns that intersect are "N" and "Q."

I want to highlight the rows in table two that have values that are in table one.

I'm not sure how to go about this though. So far I've gone down two dead end paths:

Path 1: Conditional Formatting.
If Range(Q1:Q37) = Range(N2:N147) then highlight
However it can only equal a single value at a time, and I don't care to enter 146 conditional formats. This would work if it can be programmed through VBA, but as far as I know it can't.

Path 2: A for loop within a for loop:

For t = 1 To impdatalength
check = Cells(t, 17).Value
For y = 2 To datalength
If Cells(y, 14).Value <> check Then
Next
Else
Range("q" & t & ":u" & t).ColorIndex = 4
End If
Next

But I keep getting a "for without next" error message from doing it this way. I'm not sure if VBA allows for a for within a for. Perhaps a case within a for?

**FrankBoston** · 08-22-2007, 06:29 PM

A      B       C        D     
     List 1   List 2   Found          
                         
 4     4       5        0          
 5     5       7        1          
 6     6      10        0          
 7     7                1          
 8     8                0          
 9     9                0          
10     10               1          
11     11               0          
       
       B4 Conditional Formula "=D4"
             D4  =1 - ISNA( MATCH(B4,$C$4:$C$11,0) ) 

Column C becomes 1 for a match, 0 otherwise, and is used in the 
conditional format formula to change the color. 
(See attached workbook)