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
|