MS Access - Conditional Formatting in Continuous Form on Specific Cells

Question

I'm working on Access and I have a question to ask on conditional formatting. As you can see in the picture I have 2 columns of data, L/I and Unit Price.

What I want to do is whenever there is duplicated L/I appearing in the data, conditional formatting will

1. highlight the duplicated L/I, and
2. compare on only the specific unit prices with duplicated L/I and show the cheaper price with green font colour, yellow for price in the middle and red for the most expensive price.

I have done the first part as shown in the picture but I am clueless on the expression to be used for the second part. Not sure if I have articulated my concerns accurately but thanks in advance for attending to this question. Your help is sincerely appreciated.

What my continuous form looks like:

Answer 1

Maybe you could set the record source of the form to a query like beneath that generates a 'helper'-column with the name Indicator that is filled with the text "RED", "YELLOW" or "GREEN" according to their unit price characteristic. Based on this column, inside the form you could use conditional formatting to actually show the unit price in the wanted color. Hope this query helps (you in the right direction). I assumed the table is called "Items", you should change that to your naming.

SELECT i.ID, i.LI, i.UnitPrice, iif(i.UnitPrice = (SELECT MAX(il.UnitPrice) FROM Items AS il WHERE il.LI = i.LI), "RED", iif(i.UnitPrice = (SELECT MIN(il.UnitPrice) FROM Items AS il WHERE il.LI = i.LI), "GREEN", "YELLOW" )) AS Indicator FROM Items as i;