Data Validation/Conditional Formating

[ray963]

Hi All,

I have created a data validation table with conditional formatting, so that
when I select an item, the fill colour of of the cell also changes e.g. when
selecting "YES" from the drop-down menu, it enters the word "YES" and the
fill of the cell changes to green. The same with selecting "NO", it enters
the word "NO" and changes the cell fill colour to red.

I am using Excel 2003 which only allows me to have three (3) conditional
formats per cell.

I want to select any one of a number of items from the data validation list
to allow the one colour e.g.

"YES"
"YES - BUT"
"NO"
"NO - BUT"

I want to conditional format the cell that by selecting either "YES" or "YES
- BUT" it changes the cell fill colour to green and if selecting "NO" or "NO
- BUT" it changes the cell fill colour to red.

The problem is, four choices and only three cell conditions.

Does anyone know the formula to be able to select either item to produce the
cell fill colour i.e. ="YES" or "YES - BUT" produces a green cell fill colour.

Many Thanks.
-
ray963

 

[Daniel.C]

Hi.
Use the formulas :
=LEFT(A1,3)="YES"
and
=LEFT(A1,2)="NO"
admitting A1 is the cell to format, and use formula instead of cell
value for the conditinal formatting.
HTH
Daniel

 

[muddan madhu]

condition 1
=OR($A$2="YES",$A$2="YES-BUT") choose format | ok

condition 2
=OR($A$2="No",$A$2="No-BUT") choose format | ok

 

[ray963]

Thanks muddan madhu,

Your recommendation worked perfectly for that cell.

However, I require the "Data Validation/Conditional Formatting" that we just
created for that cell to be copied and pasted to multiple cells underneath
that cell.

When I do that and select an item in cell A2 it changes all the other cell
colours below it.

With your recommendation of:

condition 1
=OR($A$2="YES",$A$2="YES-BUT") choose format ok

.....I understand the $A relates to column A and $2 refers to the cell row.

How can I adjust this formular so that I can copy and special paste it to
multiple cells below it in the column without interfering with the other
cells.