can i include a second query that can then highlight results? 1

[citizenzen]

is it really possible to include a second query within my module that will highlight found records based on the query?

i have 2 queries. the first one runs successfully. however, i want to include a second query that is also pulled from the database. with the second query, i am searching through the worksheet, and based on the results of the second query, highlight only those cells.

if someone can point me to a tutorial that shows this, i would also appreciate that as well. Or is it something simple within my code?

i am getting an 'Application- defined or object defined error' on the following line

ws_dest.Cells.Value(rng).Interior.Color = RGB(255, 255, 153)

This is the sub i am working with:

While Not rs.EOF
    
    For i = 0 To rs.Fields.Count - 1
    
    row = 2
    
    ws_dest.Range("A:C").EntireColumn.AutoFit
    
    Set rng = ws_dest.Cells(row, i + 1)
       
    ws_dest.Range("A2:C484").CopyFromRecordset rs
    
     row = row + 2
     
    Next i
    
    '2) Search through above results and highlight cells based on prs recordset
    
    For i = 0 To prs.Fields.Count - 1

    Do Until prs.EOF
        
        If (InStr(rng, prs.Fields(i))) Then
    
        ws_dest.Cells.Value(rng).Interior.Color = RGB(255, 255, 153) 'error is here
   
    End If
    Loop
    
    Next i

rs.MoveNext

Wend
End Sub

thanks in advance.

 

[PHV]

The logic in your code seems strange for me.
Anyway, I give you this starting point:

If InStr(rng, prs.Fields(i)) Then
    rng.Interior.Color = RGB(255, 255, 153)
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[citizenzen]

thank you. i was embedding the second statement within the overall query (which pulls the initial data).

so far, only one cell is highlighted, although there are more to highlight.

i am still getting an error within this second block. the program stops at the 'end if'. i put 2 variables in the watch window, and nothing seems to change much.

   Do Until prs.EOF
    
    For i = 0 To prs.Fields.Count - 1
       
        If (InStr(rng, prs.Fields(i))) Then

        rng.Interior.Color = RGB(255, 255, 153)
   
        End If
        
    Next i
    
   Loop

 

[citizenzen]

i actually refined my statement, and more cells are finally being highlighted, but there should be about 275 and I am getting only 10:

'1) Pull results from initial recordset
While Not rs.EOF
    
    For i = 0 To rs.Fields.Count - 1
    
    row = 2
    
    ws_dest.Range("A:C").EntireColumn.AutoFit
    
    Set rng = ws_dest.Cells(row, i + 1)
       
    ws_dest.Range("A2:C484").CopyFromRecordset rs
    
     row = row + 2
     
    Next i
    
    '2) Search through above results and highlight cells based on prs recordset
       
    Do Until prs.EOF
    
     For i = 0 To prs.Fields.Count - 1
     
        For Each rng In ws_dest.Cells.Range("C2:C484")
         
        If (InStr(rng, prs.Fields(i))) Then

        rng.Interior.Color = RGB(255, 255, 153)

        End If
        
        Next
        
    Next i
    
    prs.MoveNext
    
Loop
prs.Close
   
    
'end of first statement
rs.MoveNext
Wend
rs.Close

conn.Close

 

[citizenzen]

i resolved my issue!