Solved

# Simple Conditional Formatting (Traffic Lights!)

Posted on 2014-01-24
179 Views
Hi,

See attachments which should be clear.

Simple enough conditional formatting which I can't get to work.

If Sales!B2 < TrafficLights!\$C\$2 then Sales!B2 should be Orange
If Sales!B2 < TrafficLights!\$B\$2 then Sales!B2 should be Red

This logic should apply to columns B to E in Sales Spreadsheet

TrafficLights.xlsm
0
Question by:Patrick O'Dea
• 2
• 2

LVL 49

Accepted Solution

Rgonzo1971 earned 250 total points
ID: 39805941
Hi,

pls try

Regards
TrafficLightsV1.xlsm
0

LVL 32

Assisted Solution

Rob Henson earned 250 total points
ID: 39805974
Select area starting at B2 and apply Conditional Formatting by formula with the following:

For CF Orange
=AND(B2>TrafficLights!\$B2,B2<TrafficLights!\$C2)

For CF Red
=B2<TrafficLights!\$B2

Thanks
Rob H
0

LVL 49

Expert Comment

ID: 39806039
Hi,

In my solution the CF Red is before the CF Orange and I use Stop if True to avoid being Orange
with equivalent formulas to

Red:B2 < TrafficLights!\$B2
Orange: B2 < TrafficLights!\$C2

@RobHenson
Shouldn't your formula for Orange be
=AND(B2>=TrafficLights!\$B2,B2<TrafficLights!\$C2)

Regards
0

LVL 32

Expert Comment

ID: 39806108
I tried with the "Stop If True" box being ticked after first condition but didn't seem to be working for some reason, hence why I went for the less than one value for condition 1 and between two values for condition 2; indeed the second should have the >= rather than just >.

Thanks
Rob
0

Author Closing Comment

ID: 39806181
Great stuff!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question