Conditional Formatting based on Text and Value

Hi,

I am looking to conditionally format a number of cells, B1:B3. I need these to go either Red, Amber of Green dependent on the what is entered into these cells but based on a target cell, B4. This though is dependent on what text is in A1.

As an example;

If A1 = Ford then the target in B4 would be 100. This target though is subject to change based on what is in A1.

If A1 = Ford and B1 was within 5% of B4 (either greater or less) then this would turn the cell green.
If A1 = Ford and B1 was greater than 5% or more of B4 then this would turn the cell red.
If A1 = Ford and B1 was less than 5% or more of B4 then this would turn the cell amber.

Does this help or am I not explaining this correctly?

Any help appreciated, thanks.

Is the formatting conditional on A1 = Ford? For example does A1 have to = Ford AND B1 be within +/- 5% or B4 for B1 to turn green or does only the latter condition have to be true for the colour change?

BSB

The formatting is conditional to B4, the caveat to that though is that B4 can change based on what is A1.

I may have to write tens of conditions which isn't an issue its just getting it to recognise that A1 has changed and thus the conditions for B4 have too.

I hope this makes sense.

Originally Posted by Barrera006

If A1 = Ford and B1 was within 5% of B4 (either greater or less) then this would turn the cell green.

Assuming B4 = 100, if B1 is anywhere between and inclusive of 95 and 105 then these cells should be green?

Originally Posted by Barrera006

If A1 = Ford and B1 was greater than 5% or more of B4 then this would turn the cell red.

Assuming B4 = 100, if B1 is greater than 105 then the cells should be red?

Originally Posted by Barrera006

If A1 = Ford and B1 was less than 5% or more of B4 then this would turn the cell amber.

This one seems to contradict. Do you mean if this one is less than 95% of B4 (assuming B4 = 100 then B1 is less than 95)?

BSB

Apologies, yes.

OK, the attached is done with three CFs, one for each colour. It's based solely on the value in B4.

Hope this helps.

BSB

That's fantastic and a lot of help.

The question is what happens if A1 changes to lets say Vauxhall and B4 is then 120 and the conditions change? i.e. If B1 = 120 or greater then the cell is green and if B1 = less than 120 then the cell is red.

Thanks for your help but its the change in A1 that's the big struggle.

Appreciate any help.

If the formatting should be green if the value for Ford and Vauxhall (or any other car manufacturer) is +/- 5% of the value in B4 (and the same for the other colours) then this is not a problem.
It's based on the value in B1 relative to a percentage of the value in B4 rather than the actual value in B4.

BSB

The condition is based on what is in B4 but this value may change due to the value of A1. Also the conditions will change based on the value of A1.

Thanks for your help.

So Vauxhall may have different percentages of B4 that fire the conditional formatting than Ford? Any other manufacturers may again have different percentages still?

How many different sets of conditions are you looking at here?

BSB

Hi,

There are 10 different possibilities for A1, some may have 2 and others 3 conditions so possibly up to 30 conditions.

Its trying to figure the formula etc then its a "data entry" task to do all of the formulas.

Really appreciate your help on this.

I think if we could see what the file you have so far and have details of the 10 possibilities for A1 and their respective percentages for CF then that may help provide you with a solution.
There's a chance that there's a simpler solution to this than 30 sets of conditional formatting (or 3 lots with very long and complicated IF/AND/OR statements) but hard to say without a better understanding of the task in hand I'm afraid.

BSB