Nested function or VBA to create heat map of prices

Good morning
I want to create a heat map to determine if national prices are underated or overated relative to average worldprice. I don't know if this thread can be solved by VBA or just with Excel nested functions.

Here you can find what i want as final result:

Picture1.png


Here you can find my expectations:
- If the difference between antenna spain national price and antenna average world price is positive and the absolute value of the difference between antenna spain national price and antenna average world price is over 120% of the antenna average world price, so put the cell in red

- If the difference between antenna spain national price and antenna average world price is positive and the absolute value of the difference between antenna spain national price and antenna average world price is between 100% and 120% of the antenna average world price, so put the cell in orange

- If the difference between antenna spain national price and antenna average world price is negative and the absolute value of the difference between antenna spain national price and antenna average world price is between 100% and 120% of the antenna average world price, so put the cell in light green

- If the difference between antenna spain national price and antenna average world price is negative and the absolute value of the difference between antenna spain national price and antenna average world price is over 120% of the antenna average world price, so put the cell in dark green

Thank you for your help.

Hello and Welcome to Excel Forum.
I tried to use the descriptions to write the rules as the formatting in the sample doesn't match the rules in some places (please see attached file).
For red: =OR(AND(ISNUMBER(SEARCH("no",$M5)),N5>N$16*1.2),AND(ISERROR(SEARCH("no",$M5)),N5>N$17*1.2))
For orange: =OR(AND(ISNUMBER(SEARCH("no",$M5)),N5>=N$16,N5<=N$16*1.2),AND(ISERROR(SEARCH("no",$M5)),N5>=N$17,N5<=N$17*1.2))
For light green: =OR(AND(ISNUMBER(SEARCH("no",$M5)),N5<N$16,N5>=N$16*0.8),AND(ISERROR(SEARCH("no",$M5)),N5<N$17,N5>=N$17*0.8))
For dark green: =OR(AND(ISNUMBER(SEARCH("no",$M5)),N5<N$16*0.8,N5<>""),AND(ISERROR(SEARCH("no",$M5)),N5<N$17*0.8,N5<>""))
Let us know if you have any questions.

You're Welcome and thank you for the feedback. If the solution proves to be correct then please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.