Excel: Conditional Formatting 2

[klmorgan]

Hi

Is there any way to get more than Three Levels of conditional formatting?

I have four conditions that I would like to change the background to red but I can only set three.

Or can I somehow enter Four words into the Values box?

Kind regards

Keith

 

[GlennUK]

What are the four conditions exactly? You can do a lot of multiple logic in Formula Is, if the results are to have the same format.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[klmorgan]

Ive written an attendance rechare sheet for my son-in-law and I am trying to change cell background to to red if it contains the words: Sick, Hols, Bhol, Unpaid.

The cells can contain a job name or these words as abscence codes.

I am trying to use the between choice now but it pulls in some job names.

Regards

Keith

 

[xlbo]

Lots of ideas here:

thread68-223068

search string: Conditional Formatting with more than three conditions

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[klmorgan]

Thanks to you both.

Geoff's Thread will act as reading matter for weeks!

Again many thanks

It is great to have all this help on tap


Regards

Keith

 

[colinmitton]

Keith,
Just as a thought if you give each condition a number, i.e.
Sick = 1
Hols = 2
Bhol = 3
Unpaid = 4
You could change the condition to look between 1 to 4 and the format change. To make it easier to read add a column beside it and use the IF function to explain what the number mean like this :
=IF(A1=1,"sick",IF(A1=2,"Hols",IF(A1=3,"Bhol",IF(A1=4,"Unpaid",IF(A1=5,"other","unknow")))))

May help things along.

Colin

 

[combo]

Conditional formatting allows to set up to three colours in one cell, that depend on various conditions. Together with basic format there are four colours available.
Basing on the description of the problem you need only ONE condition in conditional formatting (change background colour to red). As Glenn wrote, single formula with OR funcion will handle all exceptions.

combo

 

[GlennUK]

combo is right, and the single formula I was thinking of would be similar to:

=OR(C4="Sick",C4="Hols",C4="Bhol",C4="Unpaid")

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[klmorgan]

What is the correct syntax to enter the formula in the formula is bo:

="a".OR."b".OR."c".OR."d"

The above does not seem to work

I have achieved a work around but to use the formula wold be more elegant!

Regards

Keith

 

[GlennUK]

Hi Keith, I think our posts crossed. Have a look at what I just posted.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[klmorgan]

Glenn

I can't use a single cell reference (C4) as it refers to a group of cells.

But if I omit the cell reference the code is accepted but the conditioning dosn't work.


Regards

Keith

 

[xlbo]

Keith - you CAN use a single range reference

Select all the cells you want to apply the formatting to
Enter the formula as per the syntax that Glenn has supplied, making sure that the cell you reference is the TOP LEFT cell in the selection. Ensure that there reference is RELATIVE - ie no $ anywhere and apply - excel will adjust the rtange reference for all the other cells in the selection...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[klmorgan]

Eureka the penny drops!

Thanks for all the help, it works.

Many Thanks for your patience

Keith

P.S. I don't do many spreadsheets now as I'm retired so my pleas for help will not be too regular!