Conditional Formatting

[Noz2k]

In Excel 2010 is it possible to use colour scale formatting based upon values in other cells rather than the highlighted cells?


For instance I have 3 columns with names in, and a count of how many names in the list at the bottom

name name name
name name
name name
name
name
5........3........1

Instead of formatting the row with the count in it I would like to format the rows with the names in them, based upon the count value at the bottom. So column A would be the darkest and column c the lightest

 

[Noz2k]

is this not possible?

 

[Noz2k]

Anyone know if there is a way of doing this?

 

[Gerald Higgins]

I haven't used Excel 2010, so I'm not sure exactly how it works, but I would think it is possible.

In Excel 2003, the first options when setting up Conditional Formating are
Cell Value Is
and
Formula Is

Choosing the Cell Value Is option makes the CF apply to the current cell, based on the value of the current cell.

Choosing the Formula Is option allows you to apply CF to the current cell, based on the formula, which might refer to the current cell, or some other cell, or several cells.

You can certainly do something like what you describe, in 2003.
For example, you could select all the cells containing names in column A, and then do something like

=A$10=max(A$10:C$10)

where A10:C10 contains the 3 totals.

How you set this up on 2010 I'm not sure, but I'd be pretty confident it can be done.

 

[Noz2k]

Ok, I've found the bit where you enter a formula and I can make the values all 1 colour if over a set value in a cell. But how do I do it on a sliding scale?

So a value that is 10 more than the set value is darker than the value that's only 1 more?

 

[Gerald Higgins]

Sorry I don't know, you can't do that in Excel 2003.

 

[Noz2k]

Is there a way to actually reference the colour of a cell in excel?

so like
so

If(A7>=(Average(A7:A10))*A7<(Average(A7:A10)+5), Light Red,If(A7>=(Average(A7:A10)+5)*A7=<(Average(A7:A10)+10),Red,If(A7>(Average(A7:A10)+10),Dark Red)))

 

[Gerald Higgins]

You can do it with VBA, but not with a worksheet formula.

 

[Cease]

You can apply the conditional format based on a formula.

If the count was in A10 counting A1:A9 and you wanted it a certain color if the value was greater than 5:

Select A1:A9
Conditional Formatting>New Rule, choose the "Use a formula to determine which cells to format" option, then this for the formula:
=if(A10>5,true,false)
Then set the format how you'd like it and it's done.


Hope that helps!