compare prices in sheet AND HIGHLIGHT lowest and highesr Values then change my price

[chiller]

Hi all
I have struggled with this for days, Basically i need to compare competitors prices, and highlight top and bottom values in red and green Respectively..
Then wish to compare MyPrice(F) with Cheapest price(G).

If (F) is Higher then (G)THEN select value IN(G) and reduce the value by 1% round Down to nearest whole figure and paste in NewPrice(I).

If (F) is lower then (G) THEN copy (F) DATA and paste in NewPrice(I) AND
If in any case NewPrice(I) IS lower than Buy Price(H) then highlight cell in red.

First 2 rows as examples
[TABLE="width: 621"]
<tbody>[TR]
[TD]sku[/TD]
[TD]Comp1[/TD]
[TD]Comp2[/TD]
[TD]Comp3[/TD]
[TD]Comp4[/TD]
[TD]MyPrice[/TD]
[TD]Cheapest price[/TD]
[TD]BuyPrice[/TD]
[TD]NewPrice[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]70.81[/TD]
[TD="align: right"]74.16[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]78.01[/TD]
[TD="align: right"]70.81[/TD]
[TD="align: right"]32.50[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]72.49[/TD]
[TD="align: right"]72.49[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"]66.54[/TD]
[TD="align: right"]72.49[/TD]
[TD="align: right"]59.00[/TD]
[TD="align: right"]57[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD="align: right"]88.00[/TD]
[TD="align: right"]52.00[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]52.00[/TD]
[TD="align: right"]20.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD="align: right"]33.00[/TD]
[TD="align: right"]25.00[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]25.00[/TD]
[TD="align: right"]20.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD="align: right"]98.00[/TD]
[TD="align: right"]78.00[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]21.00[/TD]
[TD="align: right"]36.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD="align: right"]88.00[/TD]
[TD="align: right"]80.00[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]25.00[/TD]
[TD="align: right"]25.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]g[/TD]
[TD="align: right"]85.00[/TD]
[TD="align: right"]12.00[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]12.00[/TD]
[TD="align: right"]21.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]h[/TD]
[TD="align: right"]68.21[/TD]
[TD="align: right"]68.21[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]68.21[/TD]
[TD="align: right"]52.00[/TD]
[TD="align: right"]43.10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Thank you

 

[Joe4]

It really depends upon the structure of your tables, how the tables/records from each table are related.
You may have a design issue. Why do you have 4 different tables with competitor prices?
I hope you do not have a separate table for each competitor. That would be bad design and make your tasks much harder to do.

 

[chiller]

The data is in different sheets in excell, which i have linked in to access.
i can do this anyway you suggest , i was thinking that if i had all seperate competitor tables
we could link the on the common value oem part number.
then run query to establish min max etc

 

[Joe4]

I would recommend creating a Union Query which links all four tables to together, and then running your Min/Max queries on that.
See: https://support.office.com/en-us/ar...3f2-8c23-29ec44acbc05?ui=en-US&rs=en-US&ad=US