conditional formatting: graded TEXT color

abhigan said:

hi all,

in conditional formatting methods there is one "format-cells-based-on-their-values". this sets a background color in a continuous tone. (ex: red to green).

the problem is it says nothing of the foreground i.e. text color. Due to this the cells having a dark shade get tough to read.

does anyone has a solution for this. how can we set the foreground color depending on background color, possibly in a similar continuous tone. this will make a good contrast.

thanks in advance

Click to expand...

I don't know what excel version you have but in 2003 you have this feature. It's the font color.

Programmatically, you could split your conditional formatting into two cases--for 0 to 50% of the way, use one font color (white or black), and for 51-100%, use the opposite color.

Perceptually, however, what you are trying to do will probably not have the desired effect you are looking for. The human eye is not good at categorizing by slight changes in hue. For example, the colors in adjacent cells impact the color perception for the cell you are looking at. I think you'd probably have better reader perception if you instead went to "bins" for the conditional formatting (such as a red-yellow-green). Also, decide if you want to shade all cells (and overwhelm the viewer), or just highlight the cells requiring further attention.

There are some really good books by Stephen Few and Edward Tufte on this whole report perception thing.

thanks john,

i am using excel 2007, here also we have this conditional-text-coloring thing. what we already have, are a variety of ways we can apply a condition. we can do that on the basis of a cell's contents, it's value, a formula, average etc. but in all of those ways the affected cells are colored just one specific way. there is only one method: format all cells based on their values which provides a continous gradient across the cells depicting a trend.

the problem is, on at least one of the edges of the gradient band, the foreground (ie Text) color will blend into background (cell) color.



to counter this, we need to regularly change the foreground color too!. we need something like:

text-color :: based-on :: cell-color

Click to expand...

now this is what (i believe) excel cant do.

let us discuss this out.....

amen!