compare and color code

[Memar]

I just want to compare the number in report A column G to numbers in report B row 7 and use a color code when the numbers do not match. What is the best function to do this without opening the tow reports at the same time all the times. Thank you for your help and time!



[h=1][FONT=&quot]Report A [/FONT]
[FONT=&quot] [/FONT]

[FONT=&quot]Column header→[/FONT]	C	D	E	F	G
Row header[FONT=&quot]↓[/FONT]	Date​	Product type​	Branch Name​	quantity Sold​	total qt sold by date and Product type
3​	1/1/2019​	123457​	South Branch	400​	1700​
4​	1/1/2019​	123457​	North Branch	300​	1700​
5​	1/1/2019​	123457​	East Branch	200​	1700​
6​	1/1/2019​	123457​	West Branch	800​	1700​
7​	1/2/2019​	123457​	South Branch	500​	2000​
8​	1/2/2019​	123457​	North Branch	500​	2000​
9​	1/2/2019​	123457​	East Branch	1000​	2000​
10​	1/3/2019​	123457​	West Branch	800​	1200​
11​	1/3/2019​	123457​	South Branch	400​	1200​

<tbody> </tbody>

[FONT=&quot] [/FONT]
[FONT=&quot] Formula used to add the total quantity sold by date and product type in G column[/FONT]
[FONT=&quot] =SUMIFS($F$3:$F$101,$C$3:$C$101,C3,$D$3:$D$101,D3)[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]

	REPORT B
	A	B	C	D
1​	Product item 123457 report
2​		1/1/2019​	1/2/2019​	1/3/2019​
3​	South Branch	400​	500​	600​
4​	North Branch	300​	500​
5​	East Branch	200​
6​	West Branch	800​		800​
7​		1700​	1000​	1400​
	Color Formatting needed in report B Cell B7, C7 etc….

<tbody>
</tbody>

[FONT=&quot] Note Report A and Report B are two different workbooks. [/FONT]
[/h]

 

[mumps]

Which workbook is the one that is usually open? A macro in the open workbook could open the second workbook automatically, make the comparison and close the second workbook. This would be done without the process being visible. What are the names of the 2 sheets being compared? Are the two workbooks saved in the same folder? If not, what is the full path to the folder in which the second workbook is found?

 

[Memar]

Report B is the one which is usually open. Yes, these two workbooks are saved in the same folder, and I also return some values from Report A to report B using a SUMPRODUCT function. The Report A sheet name is Product tracking by product item. Report B sheet name is Product sold by branch. Thank you so much for your help.

 

[mumps]

Place this macro in a standard module in the Report B workbook and run it from there. Change the sheet names (in red) to match the actual sheet names and the workbook name (in blue) to match the Report A workbook name.

Sub CompareData()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook, wkbSource As Workbook, wsDest As Worksheet, wsSrc As Worksheet, rng As Range, RngList As Object
    Dim lCol As Long, LastRow As Long
    Set RngList = CreateObject("Scripting.Dictionary")
    Set wkbDest = ThisWorkbook
    Set wsDest = wkbDest.Sheets("Product sold")
    lCol = wsDest.Cells(7, Columns.Count).End(xlToLeft).Column
    Set wkbSource = Workbooks.Open(ThisWorkbook.Path & "\" & "WorkbookA.xlsx")
    Set wsSrc = Sheets("Product tracking")
    LastRow = wsSrc.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each rng In wsSrc.Range("G3:G" & LastRow)
        If Not RngList.Exists(rng.Value) Then
            RngList.Add rng.Value, Nothing
        End If
    Next rng
    For Each rng In wsDest.Range(wsDest.Cells(7, 2), wsDest.Cells(7, lCol))
        If Not RngList.Exists(rng.Value) Then
            rng.Font.Color = vbRed
        End If
    Next rng
    wkbSource.Close False
    Application.ScreenUpdating = True
End Sub