How to find column via header name, then compare that column with another worksheet and highlight the difference.

Jpngineer

New Member
Joined
Jul 16, 2019
Messages
9
I am a newbie here, also a newbie in VBA.

I have two worksheets that I wanted to compare.

Let's say,

Sheet1
Column DWG. NO
Column SYM
Sheet2
Column DWG. NO
Column SYM
But the column position of DWG and SYM in sheet2 is not always the same so first I need to locate the positions of the column before comparing. Then highlight the comparison.

Take note also that the sheets have thousands of row data and multiple columns. But only two columns are needed to be compared.

I am using below #Code

Sub LookForMatches()
Dim rng1 As Range, rng2 As Range, i As Range, c1 As Range, c2 As Range
Dim rng3 As Range, rng4 As Range, j As Range, c3 As Range, c4 As Range
'set ranges
Set rng1 = Sheets("datax").Range("C5", Sheets("datax").Range("C" & Rows.Count).End(xlUp))
Set i = Range("DWG. NO").Find("*", Range("DWG. NO")(1), , , xlPrevious)
Set rng2 = Sheets("datay").Range("i", Sheets("datay").Range("i" & Rows.Count).End(xlUp))
Set rng3 = Sheets("datax").Range("F5", Sheets("datax").Range("F" & Rows.Count).End(xlUp))
Set j = Range("SYM").Find("*", Range("SYM")(1), , , xlPrevious)
Set rng4 = Sheets("datay").Range("j", Sheets("datay").Range("j" & Rows.Count).End(xlUp))
'reset colour
rng1.Interior.Color = 16777215
rng2.Interior.Color = 16777215
rng3.Interior.Color = 16777215
rng4.Interior.Color = 16777215
'loop values in range
For Each c1 In rng1
If Not c1.Interior.ColorIndex = 16777215 And c1 <> "" And c1 <> 0 Then
For Each c2 In rng2
If c1 = c2 And c2.Address <> c1.Address Then
c1.Interior.Color = RGB(255, 255, 0)
c2.Interior.Color = RGB(255, 255, 0)
End If
Next c2
End If
Next c1
'loop values in next range
For Each c3 In rng3
If Not c3.Interior.ColorIndex = 16777215 And c3 <> "" And c3 <> 0 Then
For Each c4 In rng4
If c3 = c4 And c4.Address <> c3.Address Then
c3.Interior.Color = RGB(255, 255, 0)
c4.Interior.Color = RGB(255, 255, 0)
End If
Next c4
End If
Next c3
End Sub

This is not working.
Error 1004 pops-up.

Thank you so much
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
welcome

Looping through thousands of rows of data like that is slow. Generally there are other approaches that truly are orders of magnitude faster.

Suggest you explain in words what needs to be done.

Also, can another column be populated with TRUE/FALSE to indicate matches? Instead of the colours? This could be more efficient. Possibly even populated via formula & hence avoiding looping.

Sample data always help too. And also the Excel version. regards, Fazza
 
Upvote 0
Hi,

Thanks fo entertaining my question. I really appreciate it.

This is what i need to be done.
I have two sheets.
datax and data y wich contains many columns and rows.

I need to compare 2 columns of datax to datay ad highlight the similar values.
However, datay column location of the 2 column data are not fix.

In sheet1 the column lets say SYM and DWG. NO location are fix in C and F while in sheet2 the SYM. and DWG. NO column are not fix.
So I wanted to use header name which is SYM. and DWG. NO to locate the columns of sheet.

With that the problem exist.

Sorry, I am not so knowledgeabe with macro vba so I do no know what to use to make it simple.
If I am not mistaken the version is 2016 and higher I do not know exactly but I am using the Microsoft Office 365 ProPlus

Thank you.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top