Data Validation/Conditional Formating



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.

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




Use the formulas :
admitting A1 is the cell to format, and use formula instead of cell
value for the conditinal formatting.

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




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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question