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.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]DATE[/TD]
[TD]FIRM[/TD]
[TD]ITEM[/TD]
[TD]ITEM COLOR[/TD]
[TD]COST[/TD]
[TD]Acc. BALANCE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]24/1/19[/TD]
[TD]John[/TD]
[TD]Rose[/TD]
[TD]Red[/TD]
[TD]45[/TD]
[TD]55[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1/2/19[/TD]
[TD]Mathew[/TD]
[TD]Lilly[/TD]
[TD]White[/TD]
[TD]20[/TD]
[TD]180[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1/2/19[/TD]
[TD]Samuel[/TD]
[TD]Rose[/TD]
[TD]Pink[/TD]
[TD]40[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]13/2/19[/TD]
[TD]Mathew[/TD]
[TD]Lilly[/TD]
[TD]White[/TD]
[TD]20[/TD]
[TD]180[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]14/2/19[/TD]
[TD]Roy[/TD]
[TD]Rose[/TD]
[TD]Red[/TD]
[TD]45[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]16/2/19[/TD]
[TD]Mathew[/TD]
[TD]Rose[/TD]
[TD]Red[/TD]
[TD]45[/TD]
[TD]55[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
















Table 1.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]DATE[/TD]
[TD]FIRM[/TD]
[TD]ITEM[/TD]
[TD]ITEM COLOR[/TD]
[TD]COST[/TD]
[TD]Acc. BALANCE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]20/1/19[/TD]
[TD]Mathew[/TD]
[TD]Sun flower[/TD]
[TD]Yellow[/TD]
[TD]60[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]20/1/19[/TD]
[TD]Roy[/TD]
[TD]Rose[/TD]
[TD]Red[/TD]
[TD]45[/TD]
[TD]55[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]25/1/19[/TD]
[TD]John[/TD]
[TD]Rose[/TD]
[TD]Red[/TD]
[TD]45[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]11/2/19[/TD]
[TD]Roy[/TD]
[TD]Rose[/TD]
[TD]Pink[/TD]
[TD]35[/TD]
[TD]65[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]15/2/19[/TD]
[TD]Roy[/TD]
[TD]Rose[/TD]
[TD]Red[/TD]
[TD]45[/TD]
[TD]-35[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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