Change cell color based on value compared to ranges

[mvm]

Hi Everyone,

I'm trying to change the fill color of a cell based on the value of the cell compared to values in several bins.

To make it easier to understand, I've uploaded a file using an example that is not exactly what I'm doing but makes it easier to understand.

Say there is a speed limit range of 60 - 70 (mph or kph, units are unimportant here)

Driver A is good if he is driving 60 - 70 (green)

Driver A should be warned if he's driving 55 to <60 or >70 to 75 (yellow)

Driver A should be ticketed is he's driving <55 or >75 (red)

I want to fill the cell of the speed of Driver A, B, C with a green, yellow, red fill or "traffic light icon" with the color corresponding to his speed.

I have tried this using the conditional formatting, but that will (as best I can tell) only work on one side of the range (like a one-tailed test in statistics) instead of both sides of the range.

I know I could code this with a massive IF AND, but wanted to know if there's an easier conditional formatting way to do this.

Thanks!

Mike

 

[p45cal]

see attached (icon sets wouldn't work (can't stop if true)).

 

[mvm]

see attached (icon sets wouldn't work (can't stop if true)).

Thank you.

I figured out that you could have multiple conditional formats per cell and can get the red,yellow, green that way.

I also discovered, as you pointed out, that I can't use icon sets because they don't work with my data.

Thanks for your help,

Mike