Prev: Please assist. Extract Number from String
Next: activesheet error
From: u473 on 13 Mar 2010 21:01
From Target List in Workbook B column A, (Activity ID),
I need to yellow shade column A (Activity ID), in workbook A
for any matching Activity ID's.
..
Detail : Workbook B, has first to be cleaned up by deleting the first
5 rows and deleting
any remaining rows that have empty cells in column A, through the last
row.
..
Workbook A : fields definition (Maximum 2,000 records)
A B C
Activity ID Activity Description Other data
..
Target List in Workbook B : fields definition (Maximum 50 records)
A B
Activity ID Status
..
Help appreciated
..
J.P.
From: marcus on 14 Mar 2010 03:17
Hi JP

Run this code from Workbook B, this is important. It assumes you have
Book A open at the time you run it. Also does not clean up the file
just looks at the filled range in WorkbookB col A and finds matches
from this point.

Take care

Marcus

Sub CheckMatches()

Dim RngCell As Range
Dim IsMatch() As Variant
Dim res As Variant
Dim lw As Long
Dim lr As Long
Dim X As Range
Dim wb As Workbook
Dim ws As Worksheet

lr = Range("A" & Rows.Count).End(xlUp).Row
Set wb = Workbooks("Book1.xls")
Set ws = wb.Sheets("Sheet1")
IsMatch() = Range("A2:A" & lr).Value
lw = ws.Range("A" & Rows.Count).End(xlUp).Row

Set X = ws.Range("A2:A" & lw)
For Each RngCell In X
res = Application.Match(RngCell.Value, IsMatch, 0)
If IsError(res) Then
'No Match
Else ' Match
RngCell.Interior.Color = vbYellow
End If
Next RngCell

End Sub
 | 
Pages: 1
Prev: Please assist. Extract Number from String
Next: activesheet error