PDA

View Full Version : Solved: MATCHROW = 0 > HIGLIGHT



trivrain
04-07-2011, 03:04 AM
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:mkay

If Matchrow > 0 Then

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

ElseIf Matchrow = 0 Then

targetWs.cell.Interior.Color = vbRed

Full module is:

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

Can anybody help ?

Thanx

Bob Phillips
04-07-2011, 03:22 AM
Try



targetWs.Interior.Color = vbRed

trivrain
04-07-2011, 04:49 AM
Thanks. Alas not... targetWs.Interior.Color = vbRed 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
04-07-2011, 04:54 AM
should read targetWs.Cells.Interior.Color = vbRed actuallt
that dooes work, but turns the whole table red...
:bawl

Bob Phillips
04-07-2011, 05:57 AM
Post the workbook, I am not seeing the problem in my testing.

trivrain
04-07-2011, 07:48 AM
I acnnot really post this data here.... :tongue:
Just tried the attached test using the actual templates but then nothing happens at all... aaahhhh

Bob Phillips
04-07-2011, 09:06 AM
Column B in IT Fina;l is empty, so your loop never starts.

trivrain
04-07-2011, 09:10 AM
Indeed ! sorry
Theen with the attached, when running the module, the whole of T1 turns red...:banghead:

trivrain
04-07-2011, 09:11 AM
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

Bob Phillips
04-07-2011, 02:48 PM
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
04-11-2011, 09:06 AM
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
04-11-2011, 09:20 AM
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
04-11-2011, 09:25 AM
being impatient for a reply LOL. I think you want.

.Rows(i).Cells.Interior.Color = vbRed

trivrain
04-11-2011, 09:55 AM
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
04-11-2011, 10:38 AM
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
04-11-2011, 11:04 AM
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


This should do for you.

trivrain
04-11-2011, 11:17 AM
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:
If Matchrow > 0 Then
.Cells(i, "BV").Value2 = targetWs.Cells(Matchrow, "E").Value2
Am afraid I would not know how to rewrite theat in your version... :blush

BrianMH
04-11-2011, 12:41 PM
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


There you go

trivrain
04-11-2011, 02:06 PM
This is perfect !
This is way beyond my basic understanding of VBA but does exactly what I need !!:mbounce:
Thank you Brian - and xld - for your help on this.