Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default Conditional Formating - dynamic data bar colors

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,


  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Conditional Formating - dynamic data bar colors

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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 199
Default Conditional Formating - dynamic data bar colors

"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

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default Conditional Formating - dynamic data bar colors

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


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7,247
Default Conditional Formating - dynamic data bar colors


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




  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default Conditional Formating - dynamic data bar colors

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



  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 199
Default Conditional Formating - dynamic data bar colors

"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

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 199
Default Conditional Formating - dynamic data bar colors

"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

  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default Conditional Formating - dynamic data bar colors

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


"Victor Delta" wrote:

"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


  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 199
Default Conditional Formating - dynamic data bar colors

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



  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 184
Default Conditional Formating - dynamic data bar colors

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?

"Victor Delta" wrote:

"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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2007: Conditional formating of multiband colors Mr. Low Excel Discussion (Misc queries) 0 May 7th 09 11:46 PM
Negative Data Bar Conditional Formating Claire Excel Discussion (Misc queries) 0 December 12th 08 09:21 PM
Data Validation/Conditional Formating ray963 Excel Discussion (Misc queries) 3 November 18th 08 11:24 PM
Conditional formating for data in 2 columns ldenning Excel Worksheet Functions 3 September 30th 08 05:34 PM
Conditional Formating working when data is entered later ConnieH Excel Worksheet Functions 1 March 19th 07 08:40 PM


All times are GMT +1. The time now is 09:04 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"