Compare two Excel sheets by SKU, Price, and Date Ranges — highlight mismatches

Question

Sheet 1: sell_in

Column	Name	Example
A	SKU	12345
B	Price	59990
C	Start Date	01-09-2024
D	End Date	30-09-2024

Sheet 2: oficial_prices

Column	Name	Example
A	SKU	12345
B	Price	59990
C	Start Date	01-09-2024
D	End Date	30-09-2024

Goal

I want to verify that every row in sell_in matches at least one row in oficial_prices based on these conditions:

1. SKU and Price must match. (There can be multiple possible matches.)

2. Start Date check: The sell_in start date must be ≥ the oficial_prices start date.

3. End Date check: The sell_in end date must be ≤ the oficial_prices end date.

If at least one row in oficial_prices meets all three conditions for a given sell_in row, it’s considered valid.

If no match is found, I want to highlight cells B, C, and D (Price, Start Date, End Date) in red in the sell_in sheet.

I can do simple SKU or price comparisons using formulas, but I need a VBA solution that loops efficiently through both sheets, considering multiple possible matches per SKU and the date logic.

Notes:
Both sheets have hundreds of rows.
Dates are in DD-MM-YYYY format.
Excel version: Microsoft 365.

• Ike: Why don't you use conditional formatting?

Answer 1

Option Explicit
Sub VerifyPrices()
 Dim wsSell As Worksheet, wsPrice As Worksheet
 Dim lastrow As Long, r As Long
 Dim sku As String, dtStart As Date, dtEnd As Date, price As Currency
 Dim dict As Object, k, ar, bMatch As Boolean
 Set dict = CreateObject("Scripting.Dictionary")
 
 With ThisWorkbook
 Set wsSell = .Sheets("sell_in")
 Set wsPrice = .Sheets("official_prices")
 End With
 
 ' build dictionary (lookup) with collection of prices/dates for each sku
 With wsPrice
 lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
 
 For r = 2 To lastrow
 sku = .Cells(r, "A")
 If Not dict.exists(sku) Then
 dict.Add sku, New Collection
 End If
 
 price = .Cells(r, "B")
 dtStart = .Cells(r, "C")
 dtEnd = .Cells(r, "D")
 dict(sku).Add Array(dtStart, dtEnd, price)
 Next
 End With
 
 ' check sell in
 With wsSell
 lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
 
 ' scan down sheet
 For r = 2 To lastrow
 sku = .Cells(r, "A")
 price = .Cells(r, "B")
 dtStart = .Cells(r, "C")
 dtEnd = .Cells(r, "D")
 bMatch = False
 
 ' match the sku
 If dict.exists(sku) Then
 
 ' check each entry
 For Each ar In dict(sku)
 If dtStart >= ar(0) And dtEnd <= ar(1) _
 And price = ar(2) Then
 bMatch = True
 Exit For
 End If
 Next
 End If
 
 ' no match
 If Not bMatch Then
 .Cells(r, "B").Resize(, 3).Interior.Color = vbRed
 Else
 .Rows(r).Interior.Pattern = xlNone
 End If
 Next
 End With
 
 MsgBox lastrow - 1 & " rows checked on " & _
 wsSell.Name, vbInformation
End Sub

Answer 2

You can apply conditional formatting for this:

Sample data tables as of the image

The formula:
=SUM(IF(($A2=$A12ドル:$A15ドル)*($B2=$B12ドル:$B15ドル)*($C2>=$C12ドル:$C15ドル)*($D2<=$D12ドル:$D15ドル),1,0))>0

The applies to range: =$B2ドル:$D5ドル

Always take care of the absolute/relative references ($) , otherwise there will be false results
To set reference to another sheet add the sheet name with the exclamation mark before the range like this Sheet2!$B2ドル:$D5ドル

enter image description here