Comparing three columns in 2 sheets

  • Thread starter Thread starter Simon
  • Start date Start date
S

Simon

I have a spreadsheet with two sheets named 'RQ' and 'HL'.

I have three columns called 'user name', 'record number'
and 'date' in both sheets.

I need to check whether each row (based on the data in the
three columns) in 'HL' is in the sheet 'RQ' and if it is
not, then colour the row is 'HL'.

The data in 'HL' is dynamic, so the ammount of rows vary.

Example

'HL'

c12345 555 13/01/04
c54321 123 13/01/04
c67890 7458 13/01/04

'RQ'

c12345 555 13/01/04
c67890 7458 13/01/04

Result

The second row in 'HL' would be coloured.
 
Simon,

Sub testit()
Dim wksS As Worksheet, wksD As Worksheet
Dim i As Long, j As Long, lngLastRowS As Long, lngLastRowD As Long
Dim blnFound As Boolean

Set wksS = Worksheets("HL")
Set wksD = Worksheets("RQ")

lngLastRowS = wksS.Cells(Rows.Count, 1).End(xlUp).Row
lngLastRowD = wksD.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lngLastRowS
blnFound = False
For j = 1 To lngLastRowD
If wksS.Cells(i, 1) = wksD.Cells(j, 1) And wksS.Cells(i, 2) =
wksD.Cells(j, 2) Then
blnFound = True
Exit For
End If
Next
If Not blnFound Then wksS.Rows(i).Interior.ColorIndex = 35
Next
End Sub

Rob
 
Thanks you,

This worked great.

Simon
-----Original Message-----
Simon,

Sub testit()
Dim wksS As Worksheet, wksD As Worksheet
Dim i As Long, j As Long, lngLastRowS As Long, lngLastRowD As Long
Dim blnFound As Boolean

Set wksS = Worksheets("HL")
Set wksD = Worksheets("RQ")

lngLastRowS = wksS.Cells(Rows.Count, 1).End(xlUp).Row
lngLastRowD = wksD.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lngLastRowS
blnFound = False
For j = 1 To lngLastRowD
If wksS.Cells(i, 1) = wksD.Cells(j, 1) And wksS.Cells(i, 2) =
wksD.Cells(j, 2) Then
blnFound = True
Exit For
End If
Next
If Not blnFound Then wksS.Rows (i).Interior.ColorIndex = 35
Next
End Sub

Rob





.
Click to expand...
 
Simon,

Small typo.
It should have read: If wksS.Cells(i, 1) = wksD.Cells(j, 1) And
wksS.Cells(i, 2) = wksD.Cells(j, 2) And wksS.Cells(i, 3) = wksD.Cells(j, 3)
Then

Rob
 
Back
Top