Conditional Format Question

[ststern45]

Hello everyone,

I need help with conditional formatting formula.

The 1st range of values in cell range R10:R12
Example
R10 = 9
R11 = 8
R12 = 2

The 2nd range of value in cell range R18:R20
Example:
R18 = 3
R19 = 5
R20 = 2

Ok this my question. Notice the value appears twice in cell R12 and R20 (Value = 2)

In cell range AC14:AE16 I would want to format a duplicate value that appears in both cell ranges a 3rd color (see below).

In cell range AC14:AE16 any value that appears in cell range R10:R12 I color the values Green.
In cell range AC14:AE16 any value that appears in cell range R18:R20 I color the values Yellow.
How do I format a value a 3rd color that appears in both cell range R10:R12 and also R18:R20 color Blue? So in this example how do I format the value 2 the color blue?

Thanks in advance!!

 

[Scott T]

For green

=OR(AC14=$R$10:$R$12)

for yellow

=OR(AC14=$R$18:$R$20)

for Blue in the rules manager click stop if true to make sure one of the other rules does not change the format
=AND(OR(AC14=$R$10:$R$12),OR(AC14=$R$18:$R$20))