Color cells those do not comply Data Validation

[BiduraKumar]

Hi Excel Gurus,

In my excel file, I have done a macro which puts data validation for a column. However, when someboday copies and pastes any thing else, then it goes. I need only those items to appear which are in data validation. In this case that cell appears without the items in data validation (yes, when we visit that cell it will prompt to select from dropdown list). I need to color Red such cells in the column where I have put data validation.
Please advise.
I have attached a spreadsheet. Here the data validation is there for the column C from range I2:I10, however, when I copied A2 and pasted in C5, no error occured though this item is not in dropdown list.
Requirement:
I need to color red such cells.
I need to know why no error occured when copy and paste happened to a field which is withing data validation.

Regards
BiduraKumar

[zbor]

What if pasted cell is one of defined values with validation?

Here, try this:

[zbor]

No, sorry, not good... Beside that I ment NOT BETWEEN but it still won't work for for example 4,5

Try this instead:

[BiduraKumar]

Hi zbor, I tried, but not working. Would you pls do this in the spreadsheet and attach. Also, may you please advise, if the work book is shared, how will it work.

[BiduraKumar]

Also, is it possible to disable paste in this column (this column only) so that this issue wont arise.

Regards
BiduraKumar

[zbor]

Here is example, slightly modified:

=AND(NOT(ISBLANK($C2));ISNA(MATCH($C2;$I$2:$I$10;0)))

[BiduraKumar]

Hi,
It is not working for me. please find the example file attached. It worked for C5 but it is not working for C11. Please advise.

[zbor]

It does working only you need to continue formating (not copy-paste same formula that it's above.. It's only for C2...).

Easiest way is to:

- select C2 and copy
- select all other cells in C column and PASTE SPETIAL -> Formats ->OK

However, copy-pasting some other value over existing C cell will delete format too and delete your conditional formating.

You need some VBA here...

[BiduraKumar]

Hi,

Would you be kind to post a VBA code for this problem. Not sure what I am missing hrere, but I am not getting it.
Regards
Bidur