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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.