Conditional Formatting on Random Numbers

[Danbrix]

Hi

Apologies if this is really straightforward but I just couldn't get my head around it.

I have a range of random numbers in B2:D20. All values in column B are red, in column C are amber and in column D are green.

When the random range is sorted into a single column A, how do I make sure that I get colours of the sorted range to match that of the original range B2:B20?

I have also put a picture of the desired output here ... http://tinypic.com/r/2rpzfxk/9

Thank you for your help.

 

[shg]

Use three conditional formats, each with a COUNTIF formula that tests whether the cell value exists in col B, C, or D.

 

[Danbrix]

I have tried countif but unable to get what is on the attached pic. It would be great if you could share an example.

 

[shg]

Select A1:Awhatever,

CF, use a formula, formula: =countif(A1, B:B) and format as the color of col B

CF, use a formula, formula: =countif(A1, C:C) and format as the color of col C

...