VBA, macro, Excel - To compare and change according to two columns

[martintoth]

Hi guys,

I am struggling with creating macro that could compare and highlight changed cells in SAMPLE file.

I have MASTER file with first two columns that have to be used together as "Primary Key". I would like to CONCATENATE 1st and 2nd column and then somehow vlookup it and highlight changes made in SAMPLE file.

The biggest problem I see is that I can't add additional columns.

Any ideas how to finish this tricky exercise?
PS: How can I upload both files?

 

[Worf]

Hi guys,

I am struggling with creating macro that could compare and highlight changed cells in SAMPLE file.

I have MASTER file with first two columns that have to be used together as "Primary Key". I would like to CONCATENATE 1st and 2nd column and then somehow vlookup it and highlight changes made in SAMPLE file.

The biggest problem I see is that I can't add additional columns.

Any ideas how to finish this tricky exercise?
PS: How can I upload both files?

Hello Martin
Welcome to the Board. What Excel version are you using?
It's true that we can't add columns to a sheet, but we can for example add sheets to a workbook...
You can't upload files to this Board, but you can place them on any online repository such as Dropbox or SkyDrive and paste the link here.

 

[martintoth]

Hello Worf,

I've reviewed that idea. I need to create macro which can compare two sheets with "primary key" column. Possibly with header.

Something like VLookup, which can highlight different cells in rows. According to primary key. If it makes sense.

Thank you for welcoming
Martin


----------------------------------------
Excel 2003 with Win7
lenovo T420, i5-2520M CPU @ 2.50, 4 GB

 

[Worf]

Hello Worf,

I've reviewed that idea. I need to create macro which can compare two sheets with "primary key" column. Possibly with header.

Something like VLookup, which can highlight different cells in rows. According to primary key. If it makes sense.

Hello Martin

I'm not sure if I am understanding correctly. Do you want to compare two columns in different sheets and highlight the differences? Could you provide an example? Here are the necessary tools:

• File link: services like DropBox or MediaFire (sites)
• Image link: services like ImageShack (site)
• Sheet snapshot: Excel Jeanie (add-in)

All this can be easily found with Google.

 

[martintoth]

Hello Worf

please check this screenshot here. I've put comment into "F" column. Hope it will be much easier to understand.

Cheers
Martin

 

[Worf]

Hello Martin

Please test the Sub below and see if it's what you want. Place the code at the workingfile.xls book. It will highlight rows when there is no match to column A at the template file.
Any doubts, post back.

Sub Martin()
Dim wa As Workbook, i%, ws As Worksheet

Workbooks.Open Filename:=ThisWorkbook.Path & "\template.xls"
Set wa = Workbooks("template.xls")
Set ws = Workbooks(ThisWorkbook.Name).Sheets("work")

For i = 3 To ws.Range("b" & Rows.Count).End(xlUp).Row
    If RngFound(1, ws.Cells(i, 2).Value, "Sheet1", wa.Name) Is Nothing Then _
        ws.Range("d" & i & ":e" & i).Interior.ColorIndex = 4
Next
       
End Sub

Function RngFound(cn%, wv, sn$, wn$) As Range
    With Workbooks(wn).Sheets(sn)
        Set RngFound = .Columns(cn).Find(What:=wv, After:=.Cells(1, cn), _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    End With
End Function

 

[martintoth]

Hello Worf

it is strange but the Macro couldn't find "template.xls" file. Even when I tried to put both files in root folder under "c:\testfolder".

Is there any option how to browse and choose file manually from any location?

By the way nice code. Can you recommend me any starting literature? I know only VBA for dummies book.

Thank you and have a nice weekend
Martin

----------------------------------------
Excel 2003 with Win7
lenovo T420, i5-2520M CPU @ 2.50, 4 GB

 

[Worf]

Hello Martin
Strange indeed. My code requires that both workbooks are in the same folder, but you did that. We are using different Excel versions; maybe it has something to do with it.
Anyway, see if the browsing example below works.

I like John Walkenbach’s books, so:
About the program: Excel Bible
On VBA: Excel Power Programming with VBA
He has other books too, you can Google him.

Sub OpenFile()
Dim Filter$, sFile$

Filter = "Excel files (*.xls),*.xls"            ' 2003 format
sFile = Application.GetOpenFilename(Filter, , "Select a file")
Application.Workbooks.Open Filename:=sFile

End Sub

 

[martintoth]

Dear Worf

I do apologize for absolutely and unacceptable late reply.

Thank you for your help, unfortunately I could not use this macro. After few discussions with my colleagues I decided to stop developing it.

I realized that the whole process of doing job is wrongly set. Even that the macro itself is absolutely easy to use and great invention it could serve us with wrong results.

Thank you for your effort and energy, I really value it.

Apologies from my side and once again, Many Thanks Worf
Martin

 

[Worf]

Hello Martin

The important thing is to reply, some people just get their answer and disappear (until they need another answer...)

If you have another request in the future, post on the forum and send me a private message so I can take a look at it.