Conditional Formating - dynamic data bar colors

[Sirpent0r]

Hi,

I am working on dashboard type tracking list. I am using the conditional
formatting Data Bars to show progress based on a 1-10 number scale. I am
using just a simple grey color for the actual data bar.

A friend asked if I could add a color scheme to the data bar such that it
changes depending upon the actual number in the cell:

1-3 = Data bar is Red
4-6 = Data bar is Yellow
7-10 = Data bar is Green

any suggestions?

thanks,

 

[Victor Delta]

Hi,

I am working on dashboard type tracking list. I am using the conditional
formatting Data Bars to show progress based on a 1-10 number scale. I am
using just a simple grey color for the actual data bar.

A friend asked if I could add a color scheme to the data bar such that it
changes depending upon the actual number in the cell:

1-3 = Data bar is Red
4-6 = Data bar is Yellow
7-10 = Data bar is Green

any suggestions?

thanks,

Just use conditional formatting 'Cell value is between 1 and 3' etc and
chose the colour via Format, Patterns.

Then add the next condition and repeat. Repeat again for the third
condition.

V

 

[Sirpent0r]

Hi Victor,

oh i should have stressed that I want to maintain the overall 1-10 scale.
Meaning that as the bar fills the 1st 3rd of the cell it is red, then changes
to yellow as the bar continues to fill the cell the next 3rd of the way, then
changes to green as it continues to fill the last 3rd of the way.

I could not find a way to do this just using the conditional formatting
options from the menu.

 

[Chip Pearson]

I don't think you can do that. The color gradient options show the
range of colors to be displayed based on a cell's value, but the cell
cannot actually show more than a single color.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Sirpent0r]

Thanks Chip,

Yea i could not see a way to do this either. I was wondering if there were a
way to apply a formula that could manipulate the data bar properties, such as
a "If / Then" type statement.

If cell value = 1,2, or 3, then data bar gradient = Red
If cell value = 4, 5, or 6, then data bar gradient = Yellow

something like that?

 

[Victor Delta]

Thanks Chip,

Yea i could not see a way to do this either. I was wondering if there were
a
way to apply a formula that could manipulate the data bar properties, such
as
a "If / Then" type statement.

If cell value = 1,2, or 3, then data bar gradient = Red
If cell value = 4, 5, or 6, then data bar gradient = Yellow

something like that?

Why not make the data bar out of 10 separate cells? Then it could be done...

V

 

[Victor Delta]

Thanks Chip,

Yea i could not see a way to do this either. I was wondering if there were
a
way to apply a formula that could manipulate the data bar properties, such
as
a "If / Then" type statement.

If cell value = 1,2, or 3, then data bar gradient = Red
If cell value = 4, 5, or 6, then data bar gradient = Yellow

something like that?

Why not make the data bar out of 10 separate cells? Then it could be done...

V

 

[Sirpent0r]

i'm not quite sure i follow that... can you elaborate?

 

[Victor Delta]

i'm not quite sure i follow that... can you elaborate?

Create your data bar out of 10 narrow cells (or three if you want to go for
thirds, as you mentioned earlier on).

Surely, you can then set the conditional formatting for each cell to change
in a way that creates the effect you described.

V

 

[Jeremy]

I've done this myself. I'm having another problem with conditional formating
though which I will post below.

To do the bar do this:

Make 3 (or more) columns next to each other the size you want each increment
to be. I was passing a percentage, that is, I had a cell somwhere else that
showed between 0% and 100%, I wanted a bar that would show red for 0-33,
yellow for 34-64, and green for 65-100 percent.

So, I had this percentage report to EACH the three cells next to each other.
I aligned the text in the cells to the right and I made three conditions as
follows:


If Cell 1 is between 0 and .33, make it red and the text black
If Cell 1 is between .34 and .64, make it yellow and make the text yellow
If Cell 1 is between .65 and 1.0, make it green and the text green

If Cell 2 is between 0 and .33, make it white and the text white
If Cell 2 is between .34 and .64, make it yellow and make the text black
If Cell 2 is between .65 and 1.0, make it green and the text green

If Cell 3 is between 0 and .33, make it white and the text white
If Cell 3 is between .34 and .64, make it yellow and make the text yellow
If Cell 3 is between .65 and 1.0, make it green and the text black

This will leave you with a bar that starting from the left is red, yellow,
or green with the value showing on the right most side of the bar...I hope
this helps, it's a little tought to explain.

Now my question, I have a column that has cells that say "Incomplete" (with
red formating) and change to "complete" (green formating) when a value I look
up is met, say if a cell is 0 somewhere else, it's incomplete, and if it's 1,
it's complete. Once it turns to complete, I want it to stay that way (until
next time I load the workbook) even if the cell I'm looking at turns back to
0. Any ideas?