Compare two columns in two sheets and highlight values

F

fahadun

New Member
Joined
Jul 27, 2017
Messages
22
hello,
I am very new to vba programming. I am facing a new problem.
What i am trying to do here,
So i have two sheet, sheet1 and sheet2. Compare between sheet1 column "C" and sheet2 column "R".


  • First highlight green,those mc# in sheet1 column "C", that are common in sheet1 and also in sheet2
  • second highlight yellow, those mc# in sheet1 column "C", that mc# is in sheet1 but not in sheet2
  • third highlight red, those mc# in sheet2 column "R", that mc# is not in sheet1 but is in sheet2

Next compare between sheet1 column "B" and sheet2 column "O".


  • fourth highlight green,those wr# in sheet1 column "B", that are common in sheet1 and also in sheet2
  • fifth highlight yellow, those wr# in sheet1 column "B", that wr# is in sheet1 but not in sheet2
  • sixth highlight red, those wr# in sheet2 column "O", that wr# is not in sheet1 but is in sheet2
Thank you.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
This is untested, but try the below. If you receive any errors, please let me know what the error is and what line it errors on.

Code: 
Public Sub fahadun()
Dim d1          As Object, _
    d2          As Object
    
Dim ws1         As Worksheet, _
    ws2         As Worksheet
    
Dim LR1         As Long, _
    LR2         As Long
    
Dim i           As Long, _
    k           As Variant
    
Dim indexGreen  As Long, _
    indexYellow As Long, _
    indexRed    As Long
    
    
indexGreen = 4
indexYellow = 6
indexRed = 3
    
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

'Compare columns C and R
Set d1 = CreateObject("Scripting.Dictionary")
Set d2 = CreateObject("Scripting.dictionary")

LR1 = ws1.Range("C" & Rows.Count).End(xlUp).Row
LR2 = ws2.Range("R" & Rows.Count).End(xlUp).Row

For i = 2 To LR1
    If Not d1.Exists(ws1.Range("C" & i).Value) Then
        d1.Add ws1.Range("C" & i).Value, i
    End If
Next i

For i = 2 To LR2
    If Not d2.Exists(ws2.Range("R" & i).Value) Then
        d2.Add ws2.Range("R" & i).Value, i
    End If
Next i

For Each k In d1.Keys
    If d2.Exists(k) Then
        ws1.Range("C" & d1(k)).Interior.colorindex = indexGreen
    Else
        ws1.Range("C" & d1(k)).Interior.colorindex = indexYellow
    End If
Next k

For Each k In d2.Keys
    If Not d1.Exists(k) Then
        ws2.Range("R" & d2(k)).Interior.colorindex = indexRed
    End If
Next k

Set d1 = Nothing
Set d2 = Nothing

'Compare columns B and O
Set d1 = CreateObject("Scripting.Dictionary")
Set d2 = CreateObject("Scripting.dictionary")

LR1 = ws1.Range("B" & Rows.Count).End(xlUp).Row
LR2 = ws2.Range("O" & Rows.Count).End(xlUp).Row

For i = 2 To LR1
    If Not d1.Exists(ws1.Range("B" & i).Value) Then
        d1.Add ws1.Range("B" & i).Value, i
    End If
Next i

For i = 2 To LR2
    If Not d2.Exists(ws2.Range("O" & i).Value) Then
        d2.Add ws2.Range("O" & i).Value, i
    End If
Next i

For Each k In d1.Keys
    If d2.Exists(k) Then
        ws1.Range("B" & d1(k)).Interior.colorindex = indexGreen
    Else
        ws1.Range("B" & d1(k)).Interior.colorindex = indexYellow
    End If
Next k

For Each k In d2.Keys
    If Not d1.Exists(k) Then
        ws2.Range("O" & d2(k)).Interior.colorindex = indexRed
    End If
Next k

End Sub
 
Upvote 0
@MrKowz , Thank you very much for your help. It's working first three conditions. but getting messed up for last 3 conditions.
 
Upvote 0
most of the values in sheet1 column b is yellow some is regular white, and all the values in sheet2 column O is red.
 
Upvote 0
I just tested this with dummy data, and it worked as intended. Can you please provide some sample data that I can run it against?


Excel 2013/2016
BC
1
2b2
3c3
4ooidijf4
5d80
6djida6
7f7
88
99
1010
1111
1250
1360
Sheet1



Excel 2013/2016
OPQR
2c4
38id9w0q6
4a10
59i020
63
Sheet2
 
Upvote 0
Unfortunately, I cannot download files on this computer (at work). Can you provide a small list I can test against?

One thing I didn't ask... are the values unique in the columns? Meaning you won't see the same value twice in Sheet1 Column C? If there are duplicate values, how do you want these handled?
 
Upvote 0
in sheet1 Both column have repetitive values. if every duplicate value is highlighted, that will be good. in sheet2 every value is unique.


sheet1
[TABLE="class: grid, width: 50, align: left"]
<tbody>[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"]ID (Work Request)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"]CPMS_CASE_ID (Work Request)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: right"]2571755[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]MC-136244
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64, align: right"]2571755[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"]MC-136244[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: right"]3923870[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]MC-138492[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: right"]2587401[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]MC-152737[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64, align: right"]2587401[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]MC-185865[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: right"]3290056[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"]MC-185865[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64, align: right"][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: right"]3860962[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]MC-208438[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 50, align: center"]
<tbody>[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]WR
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]CPMS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]3799080[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]MC-258215[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]3860962[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]MC-271790[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]3690035
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]MC-185865[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]3894677[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]

<colgroup><col width="64"></colgroup><tbody>
[TD="class: xl65, width: 64"]MC-247056[/TD]

</tbody>
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]3988244[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]MC-229830[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]3976963[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]MC-193574[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]3974162[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]MC-279640[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
i don't know why, but it's not letting me make tables. In sheet1 both columns have repetitive values, and it's in ascending order. if every repetitive value are highlighted that will be good. but in sheet2 every values are unique in both columns. i am gonna make a table when i go home.
 
Upvote 0
@MrKowz, still stuck at that problem, here is the model data
[TABLE="class: grid, width: 100, align: left"]
<tbody>[TR]
[TD]sheet1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]C
[/TD]
[TD]E[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]WR#[/TD]
[TD]MC#[/TD]
[TD]Add[/TD]
[TD]Comm
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]2571755[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]MC-136244[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]HIMROD ST-[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]2571755[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]MC-136244[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]HIMROD ST-[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]3517927[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]MC-209089[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]VAN DAM ST[/TD]
[TD]ok[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]3616397[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]MC-229268[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]QUEENS PLZ[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]3616397[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]MC-229268[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]QUEENS PLZ[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]3951681[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]218 ST[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3894677[/TD]
[TD][/TD]
[TD] DITMARS ST[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]3894677[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD] DITMARS ST[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 100, align: center"]
<tbody>[TR]
[TD]sheet2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]O
[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]WR#[/TD]
[TD]MC#[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]2571755[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]MC-136244[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]3616397[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]MC-209089[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]3860962[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
MC-185865

<tbody>
</tbody>
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]3894677[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/QUOTE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Match specific rows with a column
Replies
1
Views
201
Peter_SSs
Peter_SSs
M
  • Solved
VBA to Insert Mismatched Row in separate worksheet
2 3
Replies
21
Views
807
Peter_SSs
Peter_SSs
T
  • Question Question
VBA compare values on different sheets.
Replies
5
Views
695
rlv01
R
V
  • Question Question
copy from source sheet1 to destination sheet2 vba code
Replies
1
Views
253
Vishaal
V
J
  • Solved
VBA to compare rows in two different sheets, if match highlight cell
2
Replies
11
Views
2K
DaveB673
DaveB673

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

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