VBA question - trying to compare results in a column between 2 different spreedsheets

Zach93

New Member
Joined
May 24, 2021
Messages
7
Office Version
  1. 365
Platform
  1. MacOS
Hello all,

I am trying to write a VBA macro that will compare column D in Worksheets("Current") to column B in Worksheets("Previous").

Few things to note:

1. column B changes dynamically. For example, the name "Zach93" may exist in cell B4 in "Current" worksheet but exists in cell B7 in "Previous" worksheet. So the VBA code I'm trying to write will just ignore if a match is found.

2. There is also contents in Column A,C,D that can be ignored. I just want to compare Column B.

3. If a match is found in Column B between the 2 spreadsheets, ignore it. If a value exists in "Current" worksheet, highlight cell red. If a value exists in "Previous" worksheet, highlight cell green.

4. The spreadsheets are in the same workbook

5. There will never be a duplicate value in Column C in 1 worksheet. For example, in "Current" there will be either 1 cell containing Zach93 or 0 (never 2 or more cells).

I've tried a few things like comparing the whole spreadsheet instead of just the column but this doesn't work (as it checks cells 1:1 and if it's different, highlights it, which results in most the spreadsheet just being highlighted)

This is office 365. Let me know if I can provide any more details

The data would look something like this
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Zach93

New Member
Joined
May 24, 2021
Messages
7
Office Version
  1. 365
Platform
  1. MacOS
I should've proof-read before posting but here are a few corrections

I am trying to write a VBA macro that will compare column D in Worksheets("Current") to column B in Worksheets("Previous").

I am trying to compare Column B to B not D to B

There will never be a duplicate value in Column C in 1 worksheet. For example, in "Current" there will be either 1 cell containing Zach93 or 0 (never 2 or more cells).

there will never be a duplicate entry in Column B, Column C is irrelevant.
 

Zach93

New Member
Joined
May 24, 2021
Messages
7
Office Version
  1. 365
Platform
  1. MacOS
This is what I have so far, but it's not working as it highlights the entire column pretty much. I think it's because right now my macro is trying to compare B2 to B2 in each spreadsheet and not looking for the actual value


Sub CompareSheets()
Dim rngCell As Range

For Each rngCell In Worksheets("Current").Range("B2:B9999")
If Not rngCell = Worksheets("Previous").Cells(rngCell.Column) Then _
rngCell.Interior.Color = vbRed
Next
For Each rngCell In Worksheets("Previous").Range("B2:B9999")
If Not rngCell = Worksheets("Current").Cells(rngCell.Column) Then _
rngCell.Interior.Color = vbGreen
Next
End Sub

And so even if zach93 may exist in cell B7 in "Current" spreadsheet but exists in cell B8 in "Previous", it marks it as a different when the macro I want should just recognize the value exists in both spreadsheets, regardless of cell location.
 

Zach93

New Member
Joined
May 24, 2021
Messages
7
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

If there is any other information I can provide please let me know. I'm still stuck on this. I feel like vlookup would be the way to go but I'm not sure how to implement this through VBA
 

Zach93

New Member
Joined
May 24, 2021
Messages
7
Office Version
  1. 365
Platform
  1. MacOS
Running into the same issue unfortunately with no luck. I feel like I'm overthinking this VBA macro :(
 

Forum statistics

Threads
1,148,253
Messages
5,745,681
Members
423,967
Latest member
malayaka

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
Top