compare and color code

M

Memar

Board Regular
Joined
Sep 2, 2011
Messages
76
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]
[TABLE="class: yiv0803919020MsoNormalTable, width: 0"]
<tbody>[TR]
[TD="width: 73"][FONT=&quot]Column header→[/FONT]
[/TD]
[TD="width: 92"] C
[/TD]
[TD="width: 127"] D
[/TD]
[TD="width: 115"] E
[/TD]
[TD="width: 93"] F
[/TD]
[TD="width: 247"] G
[/TD]
[/TR]
[TR]
[TD="width: 73"]Row header[FONT=&quot]↓[/FONT]
[/TD]
[TD="width: 92, bgcolor: #D9D9D9"]
Date​
[/TD]
[TD="width: 127, bgcolor: #D9D9D9"]
Product type​
[/TD]
[TD="width: 115, bgcolor: #D9D9D9"]
Branch Name​
[/TD]
[TD="width: 93, bgcolor: #D9D9D9"]
quantity Sold​
[/TD]
[TD="width: 247, bgcolor: #D9D9D9"]total qt sold by date and Product type
[/TD]
[/TR]
[TR]
[TD="width: 73"]
3​
[/TD]
[TD="width: 92"]
1/1/2019​
[/TD]
[TD="width: 127"]
123457​
[/TD]
[TD="width: 115"]South Branch
[/TD]
[TD="width: 93"]
400​
[/TD]
[TD="width: 247"]
1700​
[/TD]
[/TR]
[TR]
[TD="width: 73"]
4​
[/TD]
[TD="width: 92"]
1/1/2019​
[/TD]
[TD="width: 127"]
123457​
[/TD]
[TD="width: 115"]North Branch
[/TD]
[TD="width: 93"]
300​
[/TD]
[TD="width: 247"]
1700​
[/TD]
[/TR]
[TR]
[TD="width: 73"]
5​
[/TD]
[TD="width: 92"]
1/1/2019​
[/TD]
[TD="width: 127"]
123457​
[/TD]
[TD="width: 115"]East Branch
[/TD]
[TD="width: 93"]
200​
[/TD]
[TD="width: 247"]
1700​
[/TD]
[/TR]
[TR]
[TD="width: 73"]
6​
[/TD]
[TD="width: 92"]
1/1/2019​
[/TD]
[TD="width: 127"]
123457​
[/TD]
[TD="width: 115"]West Branch
[/TD]
[TD="width: 93"]
800​
[/TD]
[TD="width: 247"]
1700​
[/TD]
[/TR]
[TR]
[TD="width: 73"]
7​
[/TD]
[TD="width: 92"]
1/2/2019​
[/TD]
[TD="width: 127"]
123457​
[/TD]
[TD="width: 115"]South Branch
[/TD]
[TD="width: 93"]
500​
[/TD]
[TD="width: 247"]
2000​
[/TD]
[/TR]
[TR]
[TD="width: 73"]
8​
[/TD]
[TD="width: 92"]
1/2/2019​
[/TD]
[TD="width: 127"]
123457​
[/TD]
[TD="width: 115"]North Branch
[/TD]
[TD="width: 93"]
500​
[/TD]
[TD="width: 247"]
2000​
[/TD]
[/TR]
[TR]
[TD="width: 73"]
9​
[/TD]
[TD="width: 92"]
1/2/2019​
[/TD]
[TD="width: 127"]
123457​
[/TD]
[TD="width: 115"]East Branch
[/TD]
[TD="width: 93"]
1000​
[/TD]
[TD="width: 247"]
2000​
[/TD]
[/TR]
[TR]
[TD="width: 73"]
10​
[/TD]
[TD="width: 92"]
1/3/2019​
[/TD]
[TD="width: 127"]
123457​
[/TD]
[TD="width: 115"]West Branch
[/TD]
[TD="width: 93"]
800​
[/TD]
[TD="width: 247"]
1200​
[/TD]
[/TR]
[TR]
[TD="width: 73"]
11​
[/TD]
[TD="width: 92"]
1/3/2019​
[/TD]
[TD="width: 127"]
123457​
[/TD]
[TD="width: 115"]South Branch
[/TD]
[TD="width: 93"]
400​
[/TD]
[TD="width: 247"]
1200​
[/TD]
[/TR]
</tbody>[/TABLE]
[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]
[TABLE="class: yiv0803919020MsoNormalTable, width: 0"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 219, colspan: 2"]REPORT B
[/TD]
[TD="width: 115"][/TD]
[TD="width: 93"][/TD]
[/TR]
[TR]
[TD="width: 64"][/TD]
[TD="width: 128"]A
[/TD]
[TD="width: 91"] B
[/TD]
[TD="width: 115"] C
[/TD]
[TD="width: 93"] D
[/TD]
[/TR]
[TR]
[TD="width: 64"]
1​
[/TD]
[TD="width: 219, colspan: 2"]Product item 123457 report
[/TD]
[TD="width: 115"][/TD]
[TD="width: 93"][/TD]
[/TR]
[TR]
[TD="width: 64"]
2​
[/TD]
[TD="width: 128"][/TD]
[TD="width: 91, bgcolor: #D9D9D9"]
1/1/2019​
[/TD]
[TD="width: 115, bgcolor: #D9D9D9"]
1/2/2019​
[/TD]
[TD="width: 93, bgcolor: #D9D9D9"]
1/3/2019​
[/TD]
[/TR]
[TR]
[TD="width: 64"]
3​
[/TD]
[TD="width: 128"]South Branch
[/TD]
[TD="width: 91"]
400​
[/TD]
[TD="width: 115"]
500​
[/TD]
[TD="width: 93"]
600​
[/TD]
[/TR]
[TR]
[TD="width: 64"]
4​
[/TD]
[TD="width: 128"]North Branch
[/TD]
[TD="width: 91"]
300​
[/TD]
[TD="width: 115"]
500​
[/TD]
[TD="width: 93"][/TD]
[/TR]
[TR]
[TD="width: 64"]
5​
[/TD]
[TD="width: 128"]East Branch
[/TD]
[TD="width: 91"]
200​
[/TD]
[TD="width: 115"][/TD]
[TD="width: 93"][/TD]
[/TR]
[TR]
[TD="width: 64"]
6​
[/TD]
[TD="width: 128"]West Branch
[/TD]
[TD="width: 91"]
800​
[/TD]
[TD="width: 115"][/TD]
[TD="width: 93"]
800​
[/TD]
[/TR]
[TR]
[TD="width: 64"]
7​
[/TD]
[TD="width: 128"][/TD]
[TD="width: 91"]
1700​
[/TD]
[TD="width: 115"]
1000​
[/TD]
[TD="width: 93"]
1400​
[/TD]
[/TR]
[TR]
[TD="width: 64"][/TD]
[TD="width: 128"][/TD]
[TD="width: 91"][/TD]
[TD="width: 115"][/TD]
[TD="width: 93"][/TD]
[/TR]
[TR]
[TD="width: 64"][/TD]
[TD="width: 333, colspan: 3"]Color Formatting needed in report B Cell B7, C7 etc….
[/TD]
[TD="width: 93"][/TD]
[/TR]
</tbody>[/TABLE]
[FONT=&quot] Note Report A and Report B are two different workbooks. [/FONT]
[/h]
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
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?
 
Upvote 0
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.
 
Upvote 0
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.
Code: 
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

GMC The Macro Man
  • Solved
Return the correct value that matches criteria
Replies
3
Views
148
dreid1011
dreid1011
T
  • Question Question
What if investment
Replies
2
Views
65
tigerzen
T
airforceone
  • Solved
AutoFilter Multi Columns with Date
Replies
2
Views
197
airforceone
airforceone
E
  • Solved
Find a value matching a range upon several criteria
Replies
2
Views
166
ED38
E
E
  • Question Question
How to dynamically update formula in columns
Replies
3
Views
312
eol666
E

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

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
Back
Top