Comparing three columns in 2 sheets
 

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.

Comparing three columns in 2 sheets
 

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


"Simon" wrote in message
...
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.

Comparing three columns in 2 sheets
 

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


"Simon" wrote in
message
...
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.




.

Comparing three columns in 2 sheets
 

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


wrote in message
...
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


"Simon" wrote in
message
...
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.




.