Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

Simple Conditional Formatting (Traffic Lights!)

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

Thanks for your time!
TrafficLights.xlsm
0
Patrick O'Dea
Asked:
Patrick O'Dea
  • 2
  • 2
2 Solutions
 
Rgonzo1971Commented:
Hi,

pls try

Regards
TrafficLightsV1.xlsm
0
 
Rob HensonIT & Database AssistantCommented:
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
 
Rgonzo1971Commented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
Patrick O'DeaAuthor Commented:
Great stuff!
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now