Compare two sheets based on lastrow with highlighted cells

[abdo meghari]

Hi,
I have two sheets contains data and every time the last row will change in location so what I want compare amount in lastrow for column F in INVOICE sheet with amount in lastrow for column E in ACCOUNT sheet .if the amount in last row in ACCOUNT sheet bigger than amount in last row in INVOICE sheet then will highlighted by red and write the difference for adjacent cell by plus or deficit and amount , and if it's matched the amounts between two sheets then will highlighted yellow and write matched .
will show result for both sheets in last amount in last column .
example:

COMPARE.xlsm
	B	C	D	E	F
21	ITEM	BRAND	QTY	PRICE	BALANCE
22	1	VEGA 70A R KOR	4.00	500.00	2,000.00
23	2	VEGA 55A R KOR	2.00	425.00	850.00
24	3	LARGEST 60A L HIGH JAP	2.00	440.00	880.00
25	4	KM 215/65R16 TA31 KOR	4.00	460.00	1,840.00
26	5	265/70R16 ALGERIA	4.00	630.00	2,520.00
27	6	KM 205/65R16 HS63 KOR	4.00	445.00	1,780.00
28	7	GC 385/65R22.5 AT131 CHI	2.00	1,650.00	3,300.00
29	8	KM 265/70R16 KOR	4.00	880.00	3,520.00
30	9	DONGA 66A L KOR	2.00	460.00	920.00
31	10	KM 235/65R17 HP71 KOR	4.00	625.00	2,500.00
32	11	KM 235/55R19 PS71 KOR	4.00	675.00	2,700.00
33	12	DROUB 90A L KOR	1.00	575.00	575.00
34	13	KM 13R22.5 MA11 KOR	2.00	2,550.00	5,100.00
35	TOTAL		39.00		28,485.00
INVOICE

Cell Formulas
Range	Formula
D35,F35	D35	=SUM(D22:D34)
F22:F34	F22	=D22*E22

COMPARE.xlsm
	A	B	C	D	E
1	DATE	INFO	DEBIT	CREDIT	BALANCE
2	01/11/2024	SALES INVOICE NO BSJ2000	2,000.00		2,000.00
3	02/11/2024	SALES INVOICE NO BSJ2001	3,000.00		5,000.00
4	03/11/2024	SALES RETURNS INVOICE NO BSJ789		3,000.00	2,000.00
5	03/11/2024	VOUCHER NO VBGH6788		1,500.00	500.00
6	04/11/2024	SALES INVOICE NO BSJ2003	15,000.00		15,500.00
7	04/11/2024	SALES INVOICE NO BSJ2004	13,000.00		28,500.00
8
9
10
11
12
13
14
15
ACCOUNT

Cell Formulas
Range	Formula
E2	E2	=C2-D2
E3:E7	E3	=E2+C3-D3

result

COMPARE.xlsm
	A	B	C	D	E	F
1	DATE	INFO	DEBIT	CREDIT	BALANCE
2	01/11/2024	SALES INVOICE NO BSJ2000	2,000.00		2,000.00
3	02/11/2024	SALES INVOICE NO BSJ2001	3,000.00		5,000.00
4	03/11/2024	SALES RETURNS INVOICE NO BSJ789		3,000.00	2,000.00
5	03/11/2024	VOUCHER NO VBGH6788		1,500.00	500.00
6	04/11/2024	SALES INVOICE NO BSJ2003	15,000.00		15,500.00
7	04/11/2024	SALES INVOICE NO BSJ2004	13,000.00		28,500.00	PLUS 15.00
8
9
10
11
12
13
14
15
ACCOUNT

Cell Formulas
Range	Formula
E2	E2	=C2-D2
E3:E7	E3	=E2+C3-D3

COMPARE.xlsm
	B	C	D	E	F	G
21	ITEM	BRAND	QTY	PRICE	BALANCE
22	1	VEGA 70A R KOR	4.00	500.00	2,000.00
23	2	VEGA 55A R KOR	2.00	425.00	850.00
24	3	LARGEST 60A L HIGH JAP	2.00	440.00	880.00
25	4	KM 215/65R16 TA31 KOR	4.00	460.00	1,840.00
26	5	265/70R16 ALGERIA	4.00	630.00	2,520.00
27	6	KM 205/65R16 HS63 KOR	4.00	445.00	1,780.00
28	7	GC 385/65R22.5 AT131 CHI	2.00	1,650.00	3,300.00
29	8	KM 265/70R16 KOR	4.00	880.00	3,520.00
30	9	DONGA 66A L KOR	2.00	460.00	920.00
31	10	KM 235/65R17 HP71 KOR	4.00	625.00	2,500.00
32	11	KM 235/55R19 PS71 KOR	4.00	675.00	2,700.00
33	12	DROUB 90A L KOR	1.00	575.00	575.00
34	13	KM 13R22.5 MA11 KOR	2.00	2,550.00	5,100.00
35	TOTAL		39.00		28,485.00	DEFICIT -15.00
INVOICE

Cell Formulas
Range	Formula
D35,F35	D35	=SUM(D22:D34)
F22:F34	F22	=D22*E22

another example

COMPARE.xlsm
	B	C	D	E	F
21	ITEM	BRAND	QTY	PRICE	BALANCE
22	1	VEGA 70A R KOR	4.00	500.00	2,000.00
23	2	VEGA 55A R KOR	2.00	425.00	850.00
24	3	LARGEST 60A L HIGH JAP	2.00	440.00	880.00
25	4	KM 215/65R16 TA31 KOR	4.00	460.00	1,840.00
26	5	265/70R16 ALGERIA	4.00	630.00	2,520.00
27	6	KM 205/65R16 HS63 KOR	4.00	445.00	1,780.00
28	7	GC 385/65R22.5 AT131 CHI	2.00	1,650.00	3,300.00
29	8	KM 265/70R16 KOR	4.00	880.00	3,520.00
30	9	DONGA 66A L KOR	2.00	460.00	920.00
31	10	KM 235/65R17 HP71 KOR	4.00	625.00	2,500.00
32	11	KM 235/55R19 PS71 KOR	4.00	675.00	2,700.00
33	12	XCV 90A L KOR	1.00	580.00	580.00
34	13	BJS 13R22.5 MA11 KOR	2.00	2,555.00	5,110.00
35	14	KM 13R22.5 MA11 KOR	5.00	2,000.00	10,000.00
36	TOTAL		44.00		38,500.00
INVOICE

Cell Formulas
Range	Formula
D36,F36	D36	=SUM(D22:D35)
F22:F35	F22	=D22*E22

COMPARE.xlsm
	A	B	C	D	E	F
1	DATE	INFO	DEBIT	CREDIT	BALANCE
2	01/11/2024	SALES INVOICE NO BSJ2000	2,000.00		2,000.00
3	02/11/2024	SALES INVOICE NO BSJ2001	3,000.00		5,000.00
4	03/11/2024	SALES RETURNS INVOICE NO BSJ789		3,000.00	2,000.00
5	03/11/2024	VOUCHER NO VBGH6788		1,500.00	500.00
6	04/11/2024	SALES INVOICE NO BSJ2003	15,000.00		15,500.00
7	04/11/2024	SALES INVOICE NO BSJ2004	13,000.00		28,500.00	PLUS 15.00
8	05/11/2024	SALES INVOICE NO BSJ2005	15,000.00		43,500.00
9	05/11/2024	SALES INVOICE NO BSJ2006	1,000.00		44,500.00
10	05/11/2024	VOUCHER NO VBGH6789		1,000.00	43,500.00
11	05/11/2024	VOUCHER NO VBGH6790		5,000.00	38,500.00
12
13
14
15
ACCOUNT

Cell Formulas
Range	Formula
E2	E2	=C2-D2
E3:E11	E3	=E2+C3-D3

result

COMPARE.xlsm
	B	C	D	E	F	G
21	ITEM	BRAND	QTY	PRICE	BALANCE
22	1	VEGA 70A R KOR	4.00	500.00	2,000.00
23	2	VEGA 55A R KOR	2.00	425.00	850.00
24	3	LARGEST 60A L HIGH JAP	2.00	440.00	880.00
25	4	KM 215/65R16 TA31 KOR	4.00	460.00	1,840.00
26	5	265/70R16 ALGERIA	4.00	630.00	2,520.00
27	6	KM 205/65R16 HS63 KOR	4.00	445.00	1,780.00
28	7	GC 385/65R22.5 AT131 CHI	2.00	1,650.00	3,300.00
29	8	KM 265/70R16 KOR	4.00	880.00	3,520.00
30	9	DONGA 66A L KOR	2.00	460.00	920.00
31	10	KM 235/65R17 HP71 KOR	4.00	625.00	2,500.00
32	11	KM 235/55R19 PS71 KOR	4.00	675.00	2,700.00
33	12	XCV 90A L KOR	1.00	580.00	580.00
34	13	BJS 13R22.5 MA11 KOR	2.00	2,555.00	5,110.00
35	14	KM 13R22.5 MA11 KOR	5.00	2,000.00	10,000.00
36	TOTAL		44.00		38,500.00	MATCHED
INVOICE

Cell Formulas
Range	Formula
D36,F36	D36	=SUM(D22:D35)
F22:F35	F22	=D22*E22

COMPARE.xlsm
	A	B	C	D	E	F
1	DATE	INFO	DEBIT	CREDIT	BALANCE
2	01/11/2024	SALES INVOICE NO BSJ2000	2,000.00		2,000.00
3	02/11/2024	SALES INVOICE NO BSJ2001	3,000.00		5,000.00
4	03/11/2024	SALES RETURNS INVOICE NO BSJ789		3,000.00	2,000.00
5	03/11/2024	VOUCHER NO VBGH6788		1,500.00	500.00
6	04/11/2024	SALES INVOICE NO BSJ2003	15,000.00		15,500.00
7	04/11/2024	SALES INVOICE NO BSJ2004	13,000.00		28,500.00	PLUS 15.00
8	05/11/2024	SALES INVOICE NO BSJ2005	15,000.00		43,500.00
9	05/11/2024	SALES INVOICE NO BSJ2006	1,000.00		44,500.00
10	05/11/2024	VOUCHER NO VBGH6789		1,000.00	43,500.00
11	05/11/2024	VOUCHER NO VBGH6790		5,000.00	38,500.00	MATCHED
12
13
14
15
ACCOUNT

Cell Formulas
Range	Formula
E2	E2	=C2-D2
E3:E11	E3	=E2+C3-D3

Last example

COMPARE.xlsm
	B	C	D	E	F
21	ITEM	BRAND	QTY	PRICE	BALANCE
22	1	BJS 13R22.5 MA11 KOR	10.00	2,555.00	25,550.00
23	2	KM 13R22.5 MA11 KOR	12.00	2,000.00	24,000.00
24	TOTAL		22.00		49,550.00
INVOICE

Cell Formulas
Range	Formula
D24,F24	D24	=SUM(D22:D23)
F22:F23	F22	=D22*E22

COMPARE.xlsm
	A	B	C	D	E	F
1	DATE	INFO	DEBIT	CREDIT	BALANCE
2	01/11/2024	SALES INVOICE NO BSJ2000	2,000.00		2,000.00
3	02/11/2024	SALES INVOICE NO BSJ2001	3,000.00		5,000.00
4	03/11/2024	SALES RETURNS INVOICE NO BSJ789		3,000.00	2,000.00
5	03/11/2024	VOUCHER NO VBGH6788		1,500.00	500.00
6	04/11/2024	SALES INVOICE NO BSJ2003	15,000.00		15,500.00
7	04/11/2024	SALES INVOICE NO BSJ2004	13,000.00		28,500.00	PLUS 15.00
8	05/11/2024	SALES INVOICE NO BSJ2005	15,000.00		43,500.00
9	05/11/2024	SALES INVOICE NO BSJ2006	1,000.00		44,500.00
10	05/11/2024	VOUCHER NO VBGH6789		1,000.00	43,500.00
11	05/11/2024	VOUCHER NO VBGH6790		5,000.00	38,500.00	MATCHED
12	05/11/2024	SALES INVOICE NO BSJ2007	2,000.00		40,500.00
13	05/11/2024	SALES INVOICE NO BSJ2008	3,000.00		43,500.00
14	05/11/2024	SALES INVOICE NO BSJ2009	5,000.00		48,500.00
15
ACCOUNT

Cell Formulas
Range	Formula
E2	E2	=C2-D2
E3:E14	E3	=E2+C3-D3

result

COMPARE.xlsm
	B	C	D	E	F	G
21	ITEM	BRAND	QTY	PRICE	BALANCE
22	1	BJS 13R22.5 MA11 KOR	10.00	2,555.00	25,550.00
23	2	KM 13R22.5 MA11 KOR	12.00	2,000.00	24,000.00
24	TOTAL		22.00		49,550.00	PLUS 1,050.00
INVOICE

Cell Formulas
Range	Formula
D24,F24	D24	=SUM(D22:D23)
F22:F23	F22	=D22*E22

COMPARE.xlsm
	A	B	C	D	E	F	G
1	DATE	INFO	DEBIT	CREDIT	BALANCE
2	01/11/2024	SALES INVOICE NO BSJ2000	2,000.00		2,000.00
3	02/11/2024	SALES INVOICE NO BSJ2001	3,000.00		5,000.00
4	03/11/2024	SALES RETURNS INVOICE NO BSJ789		3,000.00	2,000.00
5	03/11/2024	VOUCHER NO VBGH6788		1,500.00	500.00
6	04/11/2024	SALES INVOICE NO BSJ2003	15,000.00		15,500.00
7	04/11/2024	SALES INVOICE NO BSJ2004	13,000.00		28,500.00	PLUS 15.00
8	05/11/2024	SALES INVOICE NO BSJ2005	15,000.00		43,500.00
9	05/11/2024	SALES INVOICE NO BSJ2006	1,000.00		44,500.00
10	05/11/2024	VOUCHER NO VBGH6789		1,000.00	43,500.00
11	05/11/2024	VOUCHER NO VBGH6790		5,000.00	38,500.00	MATCHED
12	05/11/2024	SALES INVOICE NO BSJ2007	2,000.00		40,500.00
13	05/11/2024	SALES INVOICE NO BSJ2008	3,000.00		43,500.00
14	05/11/2024	SALES INVOICE NO BSJ2009	5,000.00		48,500.00	DEFICIT -1,050.00
15
ACCOUNT

Cell Formulas
Range	Formula
E2	E2	=C2-D2
E3:E14	E3	=E2+C3-D3

 

[mumps]

Sub CompareValues()
    Application.ScreenUpdating = False
    Dim invWS As Worksheet, accWS As Worksheet, lRowInv As Long, lRowAcc As Long
    Set invWS = Sheets("INVOICE")
    Set accWS = Sheets("ACCOUNT")
    lRowInv = invWS.Range("F" & Rows.Count).End(xlUp).Row
    lRowAcc = accWS.Range("E" & Rows.Count).End(xlUp).Row
    If accWS.Range("E" & lRowAcc).Value > invWS.Range("F" & lRowInv).Value Or accWS.Range("E" & lRowAcc).Value < invWS.Range("F" & lRowInv).Value Then
        accWS.Range("E" & lRowAcc).Interior.ColorIndex = 3
        accWS.Range("F" & lRowAcc) = "PLUS " & accWS.Range("E" & lRowAcc).Value - invWS.Range("F" & lRowInv).Value
        invWS.Range("F" & lRowInv).Interior.ColorIndex = 3
        invWS.Range("G" & lRowInv).Value = "DEFICIT " & invWS.Range("F" & lRowInv).Value - accWS.Range("E" & lRowAcc).Value
    ElseIf accWS.Range("E" & lRowAcc).Value = invWS.Range("F" & lRowInv).Value Then
        accWS.Range("E" & lRowAcc).Interior.ColorIndex = 6
        accWS.Range("F" & lRowAcc) = "MATCHED "
        invWS.Range("F" & lRowInv).Interior.ColorIndex = 6
        invWS.Range("G" & lRowInv) = "MATCHED"
    End If
    Application.ScreenUpdating = True
End Sub

 

[abdo meghari]

ok it just works if the value in ACCOUNT sheet bigger than value in INVOICE sheet ,but if the value in ACCOUNT sheet smaller than value in INVOICE sheet then will show the same problem !

 

[mumps]

Sub CompareValues()
    Application.ScreenUpdating = False
    Dim invWS As Worksheet, accWS As Worksheet, lRowInv As Long, lRowAcc As Long
    Set invWS = Sheets("INVOICE")
    Set accWS = Sheets("ACCOUNT")
    lRowInv = invWS.Range("F" & Rows.Count).End(xlUp).Row
    lRowAcc = accWS.Range("E" & Rows.Count).End(xlUp).Row
    If accWS.Range("E" & lRowAcc).Value > invWS.Range("F" & lRowInv).Value Then
        accWS.Range("E" & lRowAcc).Interior.ColorIndex = 3
        accWS.Range("F" & lRowAcc) = "PLUS " & accWS.Range("E" & lRowAcc).Value - invWS.Range("F" & lRowInv).Value
        invWS.Range("F" & lRowInv).Interior.ColorIndex = 3
        invWS.Range("G" & lRowInv).Value = "DEFICIT " & invWS.Range("F" & lRowInv).Value - accWS.Range("E" & lRowAcc).Value
    ElseIf accWS.Range("E" & lRowAcc).Value < invWS.Range("F" & lRowInv).Value Then
        accWS.Range("E" & lRowAcc).Interior.ColorIndex = 3
        accWS.Range("F" & lRowAcc) = "DEFICIT " & accWS.Range("E" & lRowAcc).Value - invWS.Range("F" & lRowInv).Value
        invWS.Range("F" & lRowInv).Interior.ColorIndex = 3
        invWS.Range("G" & lRowInv).Value = "PLUS " & invWS.Range("F" & lRowInv).Value - accWS.Range("E" & lRowAcc).Value
    ElseIf accWS.Range("E" & lRowAcc).Value = invWS.Range("F" & lRowInv).Value Then
        accWS.Range("E" & lRowAcc).Interior.ColorIndex = 6
        accWS.Range("F" & lRowAcc) = "MATCHED "
        invWS.Range("F" & lRowInv).Interior.ColorIndex = 6
        invWS.Range("G" & lRowInv) = "MATCHED"
    End If
    Application.ScreenUpdating = True
End Sub

 

[abdo meghari]

Great !
last thing : can you show the number as formatting like this #,##0.00 after DEFICIT or PLUS words,please?

 

[mumps]

Sub CompareValues()
    Application.ScreenUpdating = False
    Dim invWS As Worksheet, accWS As Worksheet, lRowInv As Long, lRowAcc As Long
    Set invWS = Sheets("INVOICE")
    Set accWS = Sheets("ACCOUNT")
    lRowInv = invWS.Range("F" & Rows.Count).End(xlUp).Row
    lRowAcc = accWS.Range("E" & Rows.Count).End(xlUp).Row
    If accWS.Range("E" & lRowAcc).Value > invWS.Range("F" & lRowInv).Value Then
        accWS.Range("E" & lRowAcc).Interior.ColorIndex = 3
        accWS.Range("F" & lRowAcc) = "PLUS " & Format(accWS.Range("E" & lRowAcc).Value - invWS.Range("F" & lRowInv).Value, "#,##0.00")
        invWS.Range("F" & lRowInv).Interior.ColorIndex = 3
        invWS.Range("G" & lRowInv).Value = "DEFICIT " & Format(invWS.Range("F" & lRowInv).Value - accWS.Range("E" & lRowAcc).Value, "#,##0.00")
    ElseIf accWS.Range("E" & lRowAcc).Value < invWS.Range("F" & lRowInv).Value Then
        accWS.Range("E" & lRowAcc).Interior.ColorIndex = 3
        accWS.Range("F" & lRowAcc) = "DEFICIT " & Format(accWS.Range("E" & lRowAcc).Value - invWS.Range("F" & lRowInv).Value, "#,##0.00")
        invWS.Range("F" & lRowInv).Interior.ColorIndex = 3
        invWS.Range("G" & lRowInv).Value = "PLUS " & Format(invWS.Range("F" & lRowInv).Value - accWS.Range("E" & lRowAcc).Value, "#,##0.00")
    ElseIf accWS.Range("E" & lRowAcc).Value = invWS.Range("F" & lRowInv).Value Then
        accWS.Range("E" & lRowAcc).Interior.ColorIndex = 6
        accWS.Range("F" & lRowAcc) = "MATCHED "
        invWS.Range("F" & lRowInv).Interior.ColorIndex = 6
        invWS.Range("G" & lRowInv) = "MATCHED"
    End If
    Application.ScreenUpdating = True
End Sub

 

[abdo meghari]

every thing is great !
many thanks buddy.

 

[mumps]

You are very welcome.