Sheet Comparisons

[rudogg]

Fluff provided some great code in the thread below!!!

https://www.mrexcel.com/board/threads1112978/

I have modified it a bit and I need some extra direction on it's functionality, if anyone could be so kind.

Sub Match_Our_Price()
    Dim Cl As Range, mydiffs As Integer
    Dim Dic As Object
    
    Set Dic = CreateObject("scripting.dictionary")
    With Sheets("Sheet3")
        For Each Cl In .Range("A2", .Range("A" & Rows.count).End(xlUp))
            Dic(Cl.Value) = Cl.Offset(, 1).Value
        Next Cl
    End With
    With Sheets("Sheet1")
        For Each Cl In .Range("BX2", .Range("BX" & Rows.count).End(xlUp))
            If Dic.exists(Cl.Value) Then Cl.Offset(, -58).Value = Dic(Cl.Value)
            mydiffs = mydiffs + 1
        Next Cl
    End With
    'Display a message box to demonstrate the differences
    MsgBox mydiffs & " Our Price Fields (Column R) have been Changed.", vbInformation
End Sub

So Sheet 1 is the master sheet, in which the pricing data is being updated. Sheet 3 is the data being copied from the current pricelist sent by the manufacturer. Just 2 columns for now. Column A is the MPN and column B is the Updated Price. So, if Sheet1-Column BX, exists on Sheet 3-Column A, Color the cell Green on Sheet 1, and if Sheet 3-Column B is Different than Sheet 1-Column R, then Copy Sheet 3-Column B to Sheet 1-Column R and color the cell Blue on Sheet 1.

I tried to add a message box displaying how many fields where changed but it just counted every single row on Sheet1. Thank you in advance for any help you could provide.


Sheet 1 Data

Sheet 3 Data

 

[Fluff]

Yes, you just need to put a check in to see if the cell is blank.

 

[rudogg]

If IsEmpty(Cl.Value) = False Then ?

 

[Fluff]

Like

            If Cl.Value <> "" Then Dic(Cl.Value) = Cl.Offset(, 1).Value

 

[rudogg]

Like

            If Cl.Value <> "" Then Dic(Cl.Value) = Cl.Offset(, 1).Value

Perfect! Clearly this language is not my strong suite! Thank you so much!

 

[Fluff]

My pleasure.