Check if any value in ColB, then Allow entry in that Row, Else Not to be Allowed

[acsishere]

Dear Friends,

I don't want to allow the user to enter any value in any other column if any value is not there in ColB.

For example, if B15 is Empty, then in the entire row the user should not be allowed to enter any value. Hence the colB should be the key value column and based on the same only other entries should be allowed.

Thanks in advance.

acsishere.

[ChemistB]

First highlight your range/table.

In Data Validation, Select Custom and unclick the "Ignore Blanks" then in the custom box,

Please Login or Register  to view this content.

assuming you began highlighting in row 2.

Does this work for you?

ChemistB

[acsishere]

Thanks for your speedy response.

I have a data range B:Z. Within this range in few columns ALREADY DATAVALIDATION is applied. Is it possible to apply once again datavalidation on the same?

Else, any other solution? any macro will do?

Thanks in advance,

acsishere.

[ChemistB]

It is possible depending on what data validation you have. For example, lets say you already have Column C data validation such that C>5. Your formula for Column C (starting in row 2) Custom =AND(C2>5, $B2<>"")

I'm sorry, I wouldn't know how to do it with a macro.

ChemistB

[acsishere]

Again, thanks for your reply.

I have applied data validation in certain columns, something like (in list: =SUBJECT (it's a named range).

If it is so, how to apply again the data validation for entire row other than ColB, to avoid entry in other columns of that entire row.

I think as the possibility is there to enter anywhere in the sheet, I hope the macro only will solve the problem.

Any help is highly appreciated.

Thanks in advance.

acsishere.

[ChemistB]

Place this code within your worksheet module (Right click on the worksheet tab and "View Code")

Please Login or Register  to view this content.

Let me know if it works.

ChemistB

[acsishere]

Thank you Sir. It's working very nicely.

This is very simple and neat code. Thanks a lot for your support.

acsishere.