Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Cells in Workbooks
Hi all,
Can someone please tell me if its possible to create a program that will somehow colour cells in one workbook that match cells in another workbook? For exmaple, if a cell had, say 'IBM' in one workbook and IBM in a cell in another workbook then colour that cell (or any other way of letting me know when there is a match) As always I really appreciate your help. Cheers *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Cells in Workbooks
Hi Carlton,
Assuming that the values in the first workbook are not thr result of formulas, try the following. If the values are the result of formulas, post back for revised code: Private Sub Worksheet_Change(ByVal Target As Range) Dim WB As Workbook Dim SH As Worksheet Dim rCell As Range Set WB = Workbooks("BOOK2.xls") '<<===== CHANGE Dim rng As Range Dim RngFound As Range Set rng = Intersect(Target, Range("A1:A10")) '<<===== CHANGE If Not rng Is Nothing Then For Each rCell In rng If Not IsEmpty(rCell) Then For Each SH In WB.Worksheets Set RngFound = SH.Cells.Find( _ what:=rCell.Value, _ After:=SH.Range("A1"), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not RngFound Is Nothing Then rCell.Interior.ColorIndex = 37 Exit For End If Next SH End If If RngFound Is Nothing Then rCell.Interior. _ ColorIndex = xlNone Next rCell End If End Sub Change the name of the second workbook; change the range to be colored on the active sheet. This is worksheet event code, so it needs to be placed in the worksheet's code module, not in a standard module or the ThisWorkbook module. Copy the code, right-click the worksheet tab and paste the code. Alt-F11 to return to the worksheet. --- Regards, Norman "Carlton Patterson" wrote in message ... Hi all, Can someone please tell me if its possible to create a program that will somehow colour cells in one workbook that match cells in another workbook? For exmaple, if a cell had, say 'IBM' in one workbook and IBM in a cell in another workbook then colour that cell (or any other way of letting me know when there is a match) As always I really appreciate your help. Cheers *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Cells in Workbooks
Hi Norman,
This appears to be what I need. Is there a way to get it to run like a regular macro? By 'run like a regular macro' I mean by running it like a standard macro? At the moment it runs by opening up the worksheet. Cheers mate Carlton *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Cells in Workbooks
Also,
Is it possible to add more than workbook. At the moment the program includes one book: Set WB = Workbooks("BOOK2.xls") However, could I change the formula to include a few workbooks? Cheers Carlton *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Cells in Workbooks
Hi Carlton,
Is there a way to get it to run like a regular macro Sure. From previous posts by you I incorrectly assumed you were lookiing to have the cells colored dynamically. My error - apologies. Try the following (in a standard module): Public Sub Tester() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim rCell As Range Dim RngFound As Range Set WB = Workbooks("BOOK2.xls") '<<===== CHANGE Set rng = Range("A1:A10") '<<===== CHANGE Application.ScreenUpdating = False For Each rCell In rng If Not IsEmpty(rCell) Then For Each SH In WB.Worksheets Set RngFound = SH.Cells.Find( _ what:=rCell.Value, _ After:=SH.Range("A1"), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not RngFound Is Nothing Then rCell.Interior.ColorIndex = 37 Exit For End If Next SH End If If RngFound Is Nothing Then rCell.Interior. _ ColorIndex = xlNone Next rCell Application.ScreenUpdating = True End Sub --- Regards, Norman "Carlton Patterson" wrote in message ... Hi Norman, This appears to be what I need. Is there a way to get it to run like a regular macro? By 'run like a regular macro' I mean by running it like a standard macro? At the moment it runs by opening up the worksheet. Cheers mate Carlton *** Sent via Developersdex http://www.developersdex.com *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Cells in Workbooks
Hi Carlton,
Is it possible to add more than workbook. At the moment the program includes one book: Try: Public Sub Tester() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim rCell As Range Dim RngFound As Range Dim arr As Variant Dim i As Long arr = Array("Book2.xls", "Book3.xls", "Book4.xls") '<<= CHANGE Set rng = Range("A1:A10") '<<===== CHANGE Application.ScreenUpdating = False rng.Interior.ColorIndex = xlNone For i = LBound(arr) To UBound(arr) Set WB = Workbooks(arr(i)) For Each rCell In rng If Not IsEmpty(rCell) Then For Each SH In WB.Worksheets Set RngFound = SH.Cells.Find( _ what:=rCell.Value, _ After:=SH.Range("A1"), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not RngFound Is Nothing Then rCell.Interior.ColorIndex = 37 BlFound = True Exit For End If Next SH End If Next rCell Next i Application.ScreenUpdating = True End Sub --- Regards, Norman "Carlton Patterson" wrote in message ... Also, Is it possible to add more than workbook. At the moment the program includes one book: Set WB = Workbooks("BOOK2.xls") However, could I change the formula to include a few workbooks? Cheers Carlton *** Sent via Developersdex http://www.developersdex.com *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Cells in Workbooks
Hi Norman,
Its working a treat. Thanks a lot mate. Cheers Carlton *** Sent via Developersdex http://www.developersdex.com *** |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Cells in Workbooks
Hi Carlton,
Good to hear, but see my later post for a suggested solution for your subsequent multiple workbook lookups request. Note that all the relevant workbooks must be open. As written, there is no error handling to deal with closed/missing books. --- Regards, Norman "Carlton Patterson" wrote in message ... Hi Norman, Its working a treat. Thanks a lot mate. Cheers Carlton *** Sent via Developersdex http://www.developersdex.com *** |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Cells in Workbooks
Hi Carlton,
The line: BlFound = True Is extraneous and may be deleted. --- Regards, Norman "Norman Jones" wrote in message ... Hi Carlton, Is it possible to add more than workbook. At the moment the program includes one book: Try: Public Sub Tester() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim rCell As Range Dim RngFound As Range Dim arr As Variant Dim i As Long arr = Array("Book2.xls", "Book3.xls", "Book4.xls") '<<= CHANGE Set rng = Range("A1:A10") '<<===== CHANGE Application.ScreenUpdating = False rng.Interior.ColorIndex = xlNone For i = LBound(arr) To UBound(arr) Set WB = Workbooks(arr(i)) For Each rCell In rng If Not IsEmpty(rCell) Then For Each SH In WB.Worksheets Set RngFound = SH.Cells.Find( _ what:=rCell.Value, _ After:=SH.Range("A1"), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not RngFound Is Nothing Then rCell.Interior.ColorIndex = 37 BlFound = True Exit For End If Next SH End If Next rCell Next i Application.ScreenUpdating = True End Sub --- Regards, Norman |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Cells in Workbooks
Hi Norman,
I can manage without the multiple workbooks. The program really helps me out mate. Can't thank you enough. Cheers mate. Carlton *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing Two workbooks | Excel Worksheet Functions | |||
Comparing Two Workbooks | Excel Discussion (Misc queries) | |||
comparing workbooks | Excel Discussion (Misc queries) | |||
Comparing two workbooks | Excel Programming | |||
comparing cells in different workbooks - open a certain sheet | Excel Programming |