Conditional formatting multiple conditions (arrays?)

[Pyrex238]

I posted this thread in the VBA section as I believe it will have to be done with macros and not formulas.

I have always worked with conditional formatting on a basic basis. However, I'm trying to work with more advanced rules and I'm having trouble definining how I should approach it.

Currently on my spreadsheet I have conditional formatting on B18, which is dependent on a tier - 1-5 which is calculated in cell E20.

So,

if E20 = 5 then condition formatting B18 0-104% = green, 104-105% = yellow, 105+ red
if E20 = 4 then condition formatting B18 0-119% = green, 119-120% = yellow, 120+ red
if E20 = 3 then condition formatting B18 0-124% = green, 124-125% = yellow, 125+ red
if E20 = 2 then condition formatting B18 0-129% = green, 129-130% = yellow, 130+ red
if E20 = 1 then condition formatting B18 0-134% = green, 134-135% = yellow, 135+ red

I really don't know how to set conditional formatting arrays, how exactly can I do this, I assume it has to be done with formulas, and not the conditional formatting GUI. Or does this need to be done in VBA? I really appreciate any help, I'm a little out of my experience here.

Now my experience with conditional formatting in VBA is limited - I only know basic conditional formatting in VBA such as..

Please Login or Register  to view this content.

How exactly do I specify IF values based on another forum. I've used the LOOKUP formula, but I don't think that is applicable here.

[sweep]

Hi,

I think you can achieve this using formulas, rather than going direct to VBA.

Try these formulas for activation of Green, Yellow and Red respectively.
=B18<=INDEX({104,119,124,129,134},E20)
=B18<=INDEX({105,120,125,130,135},E20)
=B18>INDEX({105,120,125,130,135},E20)

Edit: Apologies, I ran came up with this not realising I was plugged into open office and not excel. Sorry again

[Pyrex238]

Hi,

I think you can achieve this using formulas, rather than going direct to VBA.

Try these formulas for activation of Green, Yellow and Red respectively.
=B18<=INDEX({104,119,124,129,134},E20)
=B18<=INDEX({105,120,125,130,135},E20)
=B18>INDEX({105,120,125,130,135},E20)

TRTRUE
FALSE

Well, That makes sense, however excel gives me back an error when I try to use the formulas "you may not use unions, intersections, or array constants for Conditional Formatting criteria"

[NBVC]

That is correct, you can't use those formulas.

Try:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

for green, yellow and red, respectively.

If you are using XL2007 or later, place the conditions in the order above and check the Stop if True checkboxes

[Krishnakumar]

Hi,

Create defined names. That would work in CF.

For example

Define Condition1

Refers to: ={104,119,124,129,134}

[Pyrex238]

That is correct, you can't use those formulas.

Try:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

for green, yellow and red, respectively.

If you are using XL2007 or later, place the conditions in the order above and check the Stop if True checkboxes

Well, now it has a new error for me: "You cannot use relative references in Conditional Formatting criteria for color scales, data bars, and icon sets." This is kind of the same thing I've been fighting for the last week I've tried to do this. Is this some problem between the versions? I'm inputting these 3 formulas in the CF menu for a 3 color scale, should I be using a different menu? And when I'm out of the rule, I can't click the 'stop if true' box, but I presume that's because It won't accept the formula.

[NBVC]

If you want the cell to turn green, yellow or red, don't use colour scales, data sets, etc...

From the Manage Rules windows, select Use a formula to determine which cells to format and then enter the last formula first, then click format and choose colour, then click New Rule and repeat for 2nd formula and then again for the 1st formula...

So the first formula should be on top, if not move them with the up/down arrows in window.... then check the Stop if True boxes...

[Pyrex238]

If you want the cell to turn green, yellow or red, don't use colour scales, data sets, etc...

From the Manage Rules windows, select Use a formula to determine which cells to format and then enter the last formula first, then click format and choose colour, then click New Rule and repeat for 2nd formula and then again for the 1st formula...

So the first formula should be on top, if not move them with the up/down arrows in window.... then check the Stop if True boxes...

Ah, great! Now I understand - I usually only play with conditional formatting in basic terms, or in VBA - I've never done in this way, learn new things every day! I really appreciate everyone's help!