Color cells based on multiple condition

Hi,

1)If value in range B2:B21 is 800 then color yellow, if less than 800 and greater than 0 then color red.
2)If value in range B2:B21 is 480 then color yellow, if less than 480 and greater than 0 then color red.
3)If value in range B2:B21 is 600 then color yellow, if less than 600 and greater than 0 then color red.

Thanks

May be

Please Login or Register  to view this content.

Originally Posted by Blokeman

1)If value in range B2:B21 is 800 then color yellow, if less than 800 and greater than 0 then color red.
2)If value in range B2:B21 is 480 then color yellow, if less than 480 and greater than 0 then color red.
3)If value in range B2:B21 is 600 then color yellow, if less than 600 and greater than 0 then color red.

Your requirements are conflicting
480 then color yellow...but anything < 800 and > 0 is colored red?
likewise for < 600

Thanks YasserKhalil and FDibbins for the response,

attached sample

Based on dropdown list in cell F2 (sheet2), range B1:B21 will change value.

Thanks for the file, but it appears to me that your rules are still in conflict? Can you expand on what you are trying to do?

Hi FDibbins,

Based on the selection in cell F2, range B1:B21 change, the condition will change based on the value in F2.

Thanks

You are missing my question.

1)If value in range B2:B21 is 800 then color yellow, if less than 800 and greater than 0 then color red.
2)If value in range B2:B21 is 480 then color yellow, if less than 480 and greater than 0 then color red.
3)If value in range B2:B21 is 600 then color yellow, if less than 600 and greater than 0 then color red.

You want 800, 600 or 480 to be yellow, but at the same time, you want anything less than 800 to be red.

for example, if a value is 600, should it be yellow (because it is = 600) or should it be red (because it is < 800)?
what color would 601 be - and why?
what color would 599 be - and why?

Is there any solution even if we change the color? (ex. 480=yellow, 600=blue, and 800=green). if you select 480 in drop down list then the value in range B2:B21 will not go above 480 same as 800 and 600.
Here is the link how B1:B21 populate. I don't know if this help.
http://www.excelforum.com/excel-prog...-criteria.html

For your first example aren't you really trying to say this:
If the value selected in the drop down box F2 is 800
Then with cells B2:B21, color them yellow if they are 800, if they are less than 800 and greater than 0 then color them red.
So you say they won't be greater than the selection in the drop down box?
If they are 0 or less than what color, or is that not possible with your spreadsheet?

Originally Posted by skywriter

So you say they won't be greater than the selection in the drop down box?

Yes

Originally Posted by skywriter

If they are 0 or less than what color, or is that not possible with your spreadsheet?

There is no 0 value, if blank cells then no color

What about this part?

For your first example aren't you really trying to say this:
If the value selected in the drop down box F2 is 800
Then with cells B2:B21, color them yellow if they are 800, if they are less than 800 and greater than 0 then color them red.

To see where the code goes on the sheet, right click on the Sheet2 tab and choose view code.

Please Login or Register  to view this content.

Hi skywriter, it's working but can you please amend the code to automatically run every time cell L8(sheet1) change instead of choosing same value in drop down list.

Thanks

Do you mean that part

Please Login or Register  to view this content.

If so just change that part with

Please Login or Register  to view this content.

@YasserKhalil, I've done that but it color the cells with red.