Condition Formating

[julhs]

E15 can contain either “mph” or “Kmh” (as text)

Range E16:R119 contains formula and the results are dependent on the value in E15

So when E15 = “mph”, E16:R119 will contain one set of numbers and when E15 = “kmh” it will have another set

What I am trying to do is;

When E15 = “mph”, highlight cells in E16:R119 that are between 2.5 – 4.75 in one colour

But when E15 = “kmh” highlight cells in E16:R119 that are between 4.00 – 7.00 in different colour

Problem I’m trying to resolve is the range between 4.00 – 4.75 which is common to both sets of results

The obvious solution is to reference the contents of E15, and then apply the appropriate formatting conditions to E16:R119, however Condition Formatting does not like things I’ve tried

Is there a solution to this with Condition Formatting, which is what I’d like to do? Or just use VBA to do it?

 

[Fluff]

How about

=AND($E$15="mph",E16>=2.5,E16<=4.75)

 

[julhs]

How about

=AND($E$15="mph",E16>=2.5,E16<=4.75)

Thank you Fluff, perfect.
Spent TO long trying to get this.
Many thanks.
Julhs

 

[Fluff]

You're welcome & thanks for the feedback.

 

[julhs]

Thank you Fluff, perfect.
Spent TO long trying to get this.
Many thanks.
Julhs

Where I seen to be have been going wrong was using the range E16:R119 and not the first cell in the range?

 

[Fluff]

Yes, you should always look at the 1st cell in the applies to range.