conditional formatting based on a 2nd table value

[ajith]

Hello,
I am having two tables, 1 and 2. Advance orders are noted in table 2 and payment is made from the Allotment fed (for each firm, each item and item color) in table 1. The Allotment reduces on giving advance orders (Table 2) and on payments (Table 1) made.

If the difference between acc. balance against any Firm (satisfying two conditions, same item and same item color) in Table 2 and acc. balance in the next entry for the same Firm (satisfying the same two conditions) in Table 1 is less than zero, then the cell (Acc balance) against that Firm's that particular entry in Table 1 has to be highlighted yellow. ie. It should affect only the next same entry in Table 1 and should not affect same earlier entries.

Allotment for John, rose, red = 100
Allotment for Mathew, Lilly, White =200
Allotment for Samuel, rose, pink = 100
Allotment for Mathew, Lilly, White = 200
Allotment for Roy, rose, red = 100
Allotment for Mathew, rose, red =100
Allotment for Mathew, Sunflower, yellow= 100
Allotment for Roy, rose, pink = 100
TABLE 2.

	A	B	C	D	E	F
1	DATE	FIRM	ITEM	ITEM COLOR	COST	Acc. BALANCE
2	24/1/19	John	Rose	Red	45	55
3	1/2/19	Mathew	Lilly	White	20	180
4	1/2/19	Samuel	Rose	Pink	40	60
5	13/2/19	Mathew	Lilly	White	20	180
6	14/2/19	Roy	Rose	Red	45	10
7	16/2/19	Mathew	Rose	Red	45	55
8

<tbody>
</tbody>

Table 1.

	A	B	C	D	E	F
1	DATE	FIRM	ITEM	ITEM COLOR	COST	Acc. BALANCE
2	20/1/19	Mathew	Sun flower	Yellow	60	40
3	20/1/19	Roy	Rose	Red	45	55
4	25/1/19	John	Rose	Red	45	10
5	11/2/19	Roy	Rose	Pink	35	65
6	15/2/19	Roy	Rose	Red	45	-35
7
8

<tbody>
</tbody>

So in Table 1, on entering the cost in E6, the background color in F6 should become yellow.

I hope it is clear. Any clarification could be made.
Pls reply. Macros may also be suggested

Thank you