Solved: MATCHROW = 0 > HIGLIGHT

[trivrain]

Hello,

I am having issue with the last lines of the below.
I am running a VBA to compare 2 workbooks, and copy/paste some info for matching records.
I want to hihlight rows - or at least a cell of nonmatching records.
But it keeps higlighting all records... I do not know the propper line

[vba]If Matchrow > 0 Then

.Cells(i, "BV").Value2 = targetWs.Cells(Matchrow, "E").Value2

ElseIf Matchrow = 0 Then

targetWs.cell.Interior.Color = vbRed[/vba]

Full module is:

[vba]Public Sub GROUPS()
Dim targetWb As Workbook
Dim targetWs As Worksheet
Dim Lastrow As Long
Dim Matchrow As Long
Dim i As Long

Application.ScreenUpdating = False

Set targetWb = Workbooks("T1.xls")
Set targetWs = targetWb.Worksheets(1)

With Workbooks("IT FINAL.xls").Worksheets(1)

Lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 2 To Lastrow

Matchrow = 0
On Error Resume Next
Matchrow = Application.match(.Cells(i, "A").Value2, targetWs.Columns("A"), 0)
On Error GoTo 0
If Matchrow > 0 Then

.Cells(i, "BV").Value2 = targetWs.Cells(Matchrow, "E").Value2

ElseIf Matchrow = 0 Then

targetWs.cell.Interior.Color = vbRed

End If
Next i
End With[/vba]

Can anybody help ?

Thanx

[Bob Phillips]

Try

[vba]

targetWs.Interior.Color = vbRed [/vba]

[trivrain]

Thanks. Alas not... [VBA]targetWs.Interior.Color = vbRed[/VBA] is returning an error.
My main issue in any case is that the way I set it, it is higlighting a cell in ALL records instead of ONLY The non-matching ones, but I do not know why...

[trivrain]

should read [VBA]targetWs.Cells.Interior.Color = vbRed [/VBA] actuallt
that dooes work, but turns the whole table red...

[Bob Phillips]

Post the workbook, I am not seeing the problem in my testing.

[trivrain]

I acnnot really post this data here....
Just tried the attached test using the actual templates but then nothing happens at all... aaahhhh

[Bob Phillips]

Column B in IT Fina;l is empty, so your loop never starts.

[trivrain]

Indeed ! sorry
Theen with the attached, when running the module, the whole of T1 turns red...

[trivrain]

[VBA]Public Sub GROUPS()
Dim targetWb As Workbook
Dim targetWs As Worksheet
Dim Lastrow As Long
Dim Matchrow As Long
Dim i As Long

Application.ScreenUpdating = False

Set targetWb = Workbooks("T1.xls")
Set targetWs = targetWb.Worksheets(1)

With Workbooks("IT FINAL.xls").Worksheets(1)

Lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 2 To Lastrow

Matchrow = 0
On Error Resume Next
Matchrow = Application.match(.Cells(i, "A").Value2, targetWs.Columns("A"), 0)
On Error GoTo 0
If Matchrow > 0 Then

.Cells(i, "BV").Value2 = targetWs.Cells(Matchrow, "E").Value2

ElseIf Matchrow = 0 Then

targetWs.Cells.Interior.Color = vbRed

End If
Next i
End With

Set targetWs = Nothing
Set targetWb = Nothing

Application.ScreenUpdating = True
End Sub
[/VBA]

[Bob Phillips]

Okay, it doesn't find tetstest9 in the other workbook, so it colours all rows red just like your code tells it to. What would you like to happen in this instance?

[trivrain]

Hi,
In the case tetstest9 cannot be find i would like the row of testest9 only to be colored in the targetwb (T1), so that I know that this one was not found.

[BrianMH]

So if testest9 isn't found you wan't what row in T1 red? The row 2 as it is in IT Final.xls or did you want to highlight row 2 IN IT Final.xls to indicate it wasn't found?

[BrianMH]

being impatient for a reply LOL. I think you want.

[VBA].Rows(i).Cells.Interior.Color = vbRed[/VBA]

[trivrain]

Almost there !!
Sorry I was unclear, it's almost the other way around:

I want the rows in T1 that do not match in IT FINAL to be highlighted in T1. (or even better, to be copied in a new Worksheet in T1 ). In this instance, rows 2 and 4 should be red in T1.

[BrianMH]

t1 row 4 says testtest2 and this does appear on row 7 on IT Final. Just to be clear this wouldn't be red right?

[BrianMH]

[VBA]Public Sub GROUPS()
Dim targetWb As Workbook
Dim targetWs As Worksheet
Dim wbTest As Workbook
Dim Lastrow As Long
Dim Matchrow As Long
Dim i As Long
Dim c As Range
Dim rnFindValues As Range
Dim rnFindIn As Range
Dim rnFound As Range
Application.ScreenUpdating = False

Set targetWb = Workbooks("T1.xls")
Set targetWs = targetWb.Worksheets(1)
Set rnFindIn = Workbooks("IT FINAL.xls").Worksheets(1).Columns(1)

Lastrow = targetWs.Range("A" & targetWs.Rows.Count).End(xlUp).Row
Set rnFindValues = targetWs.Range("A2:A" & Lastrow)
For Each c In rnFindValues

Set rnFound = rnFindIn.Find(c.Value2)
If rnFound Is Nothing Then
c.EntireRow.Interior.Color = vbRed
End If
Next c

Set targetWs = Nothing
Set targetWb = Nothing

Application.ScreenUpdating = True
End Sub
[/VBA]

This should do for you.

[trivrain]

Ah yes Brian, sorry my concentration is fragmenting... T1 row4 should not go red.
Ok with this version the color shows perfectly where it should !

But now we are missing the part where the content of T1's column 5 was being pasted in in IT FINAL's column BV for matching records. In the initial version it went:
[VBA]If Matchrow > 0 Then
.Cells(i, "BV").Value2 = targetWs.Cells(Matchrow, "E").Value2[/VBA]
Am afraid I would not know how to rewrite theat in your version...

[BrianMH]

[VBA]Public Sub GROUPS()
Dim targetWb As Workbook
Dim targetWs As Worksheet
Dim wbTest As Workbook
Dim wsTest As Worksheet
Dim Lastrow As Long
Dim Matchrow As Long
Dim i As Long
Dim c As Range
Dim rnFindValues As Range
Dim rnFindIn As Range
Dim rnFound As Range
Application.ScreenUpdating = False

Set targetWb = Workbooks("T1.xls")
Set targetWs = targetWb.Worksheets(1)
Set wbTest = Workbooks("IT Final.xls")
Set wsTest = wbTest.Sheets(1)

Set rnFindIn = wsTest.Columns(1)

Lastrow = targetWs.Range("A" & targetWs.Rows.Count).End(xlUp).Row
Set rnFindValues = targetWs.Range("A2:A" & Lastrow)
For Each c In rnFindValues

Set rnFound = rnFindIn.Find(c.Value2)
If rnFound Is Nothing Then
c.EntireRow.Interior.Color = vbRed
Else
wsTest.Cells(rnFound.Row, "BV").Value2 = c.Offset(0, 4)
End If

Next c

Set targetWs = Nothing
Set targetWb = Nothing

Application.ScreenUpdating = True
End Sub
[/VBA]

There you go

[trivrain]

This is perfect !
This is way beyond my basic understanding of VBA but does exactly what I need !!
Thank you Brian - and xld - for your help on this.