Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Marking found records in Workbook A from Target List in Workbook B
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Marking found records in Workbook A from Target List in WorkbookB
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Target Workbook Dialog | Excel Programming | |||
Target workbook dialog | Excel Programming | |||
Marking records with mismatched data | Excel Programming | |||
Copy marked records from Workbook to Workbook | Excel Programming | |||
Marking all lines with same value when a second value is found | Excel Discussion (Misc queries) |