Can logical operators be used within Conditional Formatting?

R

Rumpa Biswas

My requirement is to use 3 types of output format with 10 possible cell
(input) values.

Example of the problem -

If cell value = "A" OR "B" OR "C", backcolor = Red
If cell value = "D" OR "E" OR "F" OR "G" OR "H" OR "I", backcolor = Green
If cell value = "J" OR "", backcolor = Yellow

I am aware of that VB programming can be done for this.
But I was thinking, if logical operators (e.g., AND, OR) can be used within
a conditional formatting, this problem can be solved very easily. (I tried by
myself, but could work it out.)

If anybody is aware of this, please let me know.
 
A

Andrew Taylor

Change the drop-down from "Cell value is" to "Formula is", and
enter a formula like

=OR(A1="A",A1="B",A1="C")

and similarly for the other two conditions.
 
K

Ken Johnson

Hi Rumpa,
1. select A1 then Format>Conditional format....
2. In the Formula Is box type =OR(A1="A", A1="B", A1="C") then set the
red fill format
3.Click Add>> to goto the second condition
4.In the Formula Is box type =OR(A1="D", A1="E", A1="F", A1="G",
A1="H", A1="I") then set the green fill format.
5.Click Add>> to go to the third condition
6.In the Formula Is box type =OR(A1="J", A1="", A1="F") then set the
yellow fill format.
7. Click OK then copy the format to cells you want affected.

Ken Johnson
 
K

Ken Johnson

Hi Rumpa,
I'm trying again because nothing seemed to happen last time. If my
previous reply does appear I apologise for appearing repetitive.


1. Select A1 then Format>Conditional format....
2. In the Formula Is box type =OR(A1="A", A1="B", A1="C") then set the
red fill format
3. Click Add>> to goto the second condition
4. In the Formula Is box type =OR(A1="D", A1="E", A1="F", A1="G",
A1="H", A1="I") then set the green fill format.
5. Click Add>> to go to the third condition
6. In the Formula Is box type =OR(A1="J", A1="", A1="F") then set the
yellow fill format.
7.Click OK then copy the format to cells you want affected.

Ken Johnson
 
R

Roger Govier

Hi

In the Conditional Formatting dialogue, choose Formula is then set first
condition
=A1<"D" and set format to Red
Add
=A1<"J" and set format to Green
add
=A1>="J" and set format to Yellow

Change cell reference from A1 to whatever is appropriate
 
Top