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,

"Sirpent0r" wrote in message
...
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

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.


"Victor Delta" wrote:

"Sirpent0r" wrote in message
...
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

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)

On Thu, 3 Sep 2009 15:24:02 -0700, Sirpent0r
wrote:

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.


"Victor Delta" wrote:

"Sirpent0r" wrote in message
...
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

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?




"Chip Pearson" wrote:


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)

On Thu, 3 Sep 2009 15:24:02 -0700, Sirpent0r
wrote:

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.


"Victor Delta" wrote:

"Sirpent0r" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

Adding Color Scheme to Data Bars in Excel Using Conditional Formatting

1. Select the cells that contain the data bars you want to format.
2. Click on the "Conditional Formatting" button in the "Home" tab of the ribbon.
3. Select "New Rule" from the drop-down menu.
4. In the "New Formatting Rule" dialog box, select "Format only cells that contain" from the "Select a Rule Type" section.
5. In the "Edit the Rule Description" section, choose "Cell Value" from the first drop-down menu, "between" from the second drop-down menu, and enter "1" and "3" in the two boxes.
6. Click on the "Format" button and select the "Fill" tab.
7. Choose the red color you want to use for the data bars and click "OK".
8. Click "OK" again to close the "New Formatting Rule" dialog box.
9. Repeat steps 4-8 for the other two ranges (4-6 and 7-10), using yellow and green colors respectively.

Once you've set up these rules, the data bars in your selected cells will change color based on the value in the cell. If the value is between 1 and 3, the data bar will be red. If it's between 4 and 6, it will be yellow, and if it's between 7 and 10, it will be green.