I am trying to set up a Journal entry form for our accounts department. Part of the form consists of:

Column B - Account number and description
Column C - Debit Amount
Column D - Credit Amount

I want to use Data Validation on Columns C and D. The rules for each row are:

1) NO entry in either columns C or D if Column B is blank.
2) On positive amounts are allowed.
3) Can not have an amount in both columns C and D.

I have the following custom validations set:

Column B - Limited to List

Column C - =AND(LEN(B10)>6,C10>0,D10=0)

Column D - =AND(LEN(B10)>6,C10=0,D10>0)

1st Problem

My problem is that the Data validation prevents only some of the invalid entries, i.e.:
- Validation works in column C.
- Validation for column D prevents entry of positive amount if column C already entered, but does not prevent entry of negative amount. Also entry is allowed even if column B is blank.

2nd Problem

I am running a macro which unlocks all the cells the user can enter data into, including the above mentioned columns B, C and D. However, Data validation prevents All entries in columns C and D, even valid entries.

I would prefer to use Data validation, as I want to prevent the user from entering invalid data, and not test for validity afterwards via vba.

I would appreciate any help to sort this out.

Thank you.