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

[Zach93]

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

 

[Zach93]

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]

Bump

 

[Zach93]

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]

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]

Running into the same issue unfortunately with no luck. I feel like I'm overthinking this VBA macro

 

[Zach93]

One more bump