Can logical operators be used within Conditional Formatting?

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.

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.



Rumpa Biswas wrote:
> 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.

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

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

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

--
Regards

Roger Govier



Rumpa Biswas <[email protected]> wrote:
> 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.

Hi Rumpa,
Ignore that ,A1="F" in step 6.
Ken Johnson

With formula is, it worked fine. Thanks to everybody.

- Rumpa

"Rumpa Biswas" wrote:

> 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.
>

Hi I want to apply to all values a sort of condition where i say
"If cell value is greater than A1 OR lesser than B1, apply red background"

Basically I would like to highlight all data that falls out of a given range

any suggestions?