Conditional formatting according to given values

[JackXcel]

Heya,

I'm trying to apply conditional formatting on a table, based on the values given in a different table. I've attached a picture of an exemplary situation; the left table shows the average values for certain results in regards of vehicles(cars,bikes etc) from all the manufacturers in my database, divided into groups by their years of manufacture. The right table shows the same results for a certain manufacturer only.

So then, I'd like to have the table on the right to be formatted according to how the values differentiate from the table on the left. I.e. "cost X" on the right table should be colored red if it's above the value found for the "cost X" in the left table. Respectively, it should be colored green if the value is lower than the comparing value in the left. Awesome bonus would be if I could make it so, that the colors change brightness after how much the values on the right differ from the given averages. For an example; lower than average at all - light green, more than 10% below the average - green, more than 20% below the average - dark green. And the same for the reds on the higher than- side. (these percentual values might be different in reality, for in some cases even 50% below/above is not that much, but you get the idea)

Hopefully this was not too vaguely explained or in a wrong section. Thank you in advance for all possible answers.

-JackXcel

[newdoverman]

Here is a simple example of what you are trying to do. The range on the right will have highlighted the values that are greater or less than the same relative cell in the range on the left. The range on the left is the opposite of the range on the right.
The first basic Conditional Formatting rule applied to the range on the right (select the range on the right) then the formatting is chosen:

Please Login or Register  to view this content.

This will highlight if greater than the cell in the left range:

The range on the left (selected) to have the opposite effect in that if less than the cell in the right range, highlight in the format of your choice.

Please Login or Register  to view this content.

If cells in both ranges not blank but equal value:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

If you want to have different shading for different value differences, you will need to have a rule for each difference that you want for each range. The formulae would be along these lines =h3-b3=1 H3-B3=2 etc.....a whole bunch of rules.

[JackXcel]

Alright, thanks for the explanation and especially the example table! After a while I managed to figure it out and format the table as I wanted.

[newdoverman]

Thank you for the feedback. I'm glad that you have something that you can work with.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.