3-Color Scale Cond. Form. not working

[stuck]

I must be doing something wrong...

I want cell B6 to be shaded in one of three colours depending on the value in cell C6 so, having selected cell B6, in the CF dialog:
    - in the top pane, selected Rule Type 'format all cells based on their values'
    - in the bottom pane:
        selected format style '3-color scale'
        for min, mid & max, selected:
            type 'formula'
            value =$C$6<7, =OR($C$6=7,$C$6=8), =$C$6>8
            color pale red, pale orange, white
The rule then applies to =$B$6

However, no matter what value is in cell C6, B6 does not get shaded.

Please can someone show me the in this?

thanks,

Ken

[HansV]

A three-color scale rule is intended to be applied to a range of cells. It won't work to color single cells.

Instead, create two rules of type 'Use a formula to determine which cells to format'.

In the first rule, use the formula

=$C$6<=8

Click Format..., activate the Fill tab and select pale orange.
Click OK twice.

In the first rule, use the formula

=$C$6<7

Click Format..., activate the Fill tab and select pale red.
Click OK twice.

Values greater than 8 will automatically have no fill. If you want white fill, create a third rule like the first two.

S0185.png

[stuck]

A three-color scale rule is intended to be applied to a range of cells. It won't work to color single cells...

Ah, OK, I knew it would be a thing and yes, your suggested workaround will do the job.

Thanks!

Ken

[p45cal]

HansV,
my feeling is that 2 things are being misinterpreted with scalar colouring:
1. It's only available for the Rule Type 'Format all cells based on their values' , which means the conditional formatting can only look at the values of cells where the conditional formatting is to be applied, and not to cells outside that area.
2. When you choose Formula for the Type field for the Min, Mid and Max values, it wants to see a formula to determine those points/thresholds, not to look at values in other cells to determine what colour to give to the CF cells. (If you chose Number in the Type field, it would expect a plain number in the Value field representing the threshold. When a formula is used, it's there to determine the threshold (the plain value mentioned above) from other values in cells (note that it won't accept relative references in the formula).

You can apply scalar conditional formatting to a single cell, although with some of the options available it shows that it was intended largely for multiple cell ranges.

If you really, really want to apply the same colour to column B as would be applied to column C scalar colouring you will need to resort to a little vba, but then I'd struggle using only scalar conditional formatting to get values 6 AND 7 to be the same colour (what happens to values between 6 & 7, say 6.4? Do they exist?)

I'll have a go for you, but you must attach a file to play with so that we know what you're trying to do.
It'll involve the likes of:

Code: Select all

cll.Interior.Color = cll.Offset(, 10).DisplayFormat.Interior.Color

[stuck]

..
I'll have a go for you, but you must attach a file to play with so that we know what you're trying to do...

Please note, it wasn't Hans who was having trouble with this type of CF, it was me, I started this thread.

Thank you for this extra information and your offer to look into this further but I can't attach a file (commercial confidentiality). Meanwhile, the simple fix suggested by Hans revealed I was over complicating my problem and two much simpler CF rules did a far better job than a 3 colour CF rule was ever going to do.

Ken

[p45cal]

I tend to agree with you.