Macro to check for NULL values in other cells

[mrw1reless]

Hi, I have a spreadsheet where users can type in a number in Row 1 Col A. They are suppose to ensure that there are no Null values in Col E and Col F but they (of course) never do.

Is there a way to write a macro to perform a function where:

User enters a number in Row 1 Col A. The Macro checks the number. If there is no text data in Row 1 Col E AND no data in Row 1 Col F, then don't allow any entry in Row 1 Col A UNLESS the number is between 500 and 600. If so, don't do a check against Row 1 Col E or Row 1 Col F.

[DonkeyOte]

When you say Null do you mean Blank ?

Have you thought perhaps of using Data Validation, eg highlighting A1 and applying a Custom Validation rule of:

=AND(ISNUMBER(A1),OR(AND(A1>=500,A1<=600),COUNTBLANK(E1:F1)=0))

Not 100% watertight but the above may suffice in this instance.

[mrw1reless]

Hi Thank you so much for your response !! The cells ate NULL and the COUNTBLANK needs to look at cell F1 and H1 not F1:H1. I tried a nested COUNTBLANK(F1) or COUNTBLANK(H1) but it doesn't seem to work.

Can you offer a suggestion?

Regards,

Sandy

[DonkeyOte]

In which case perhaps:

=AND(ISNUMBER(A1),OR(AND(A1>=500,A1<=600),SUM(COUNTBLANK(F1),COUNTBLANK(H1))=0))

COUNT & COUNTA both accept non-contiguous ranges but if you use Nulls then COUNTA will give misleading results...it's not clear what data type the values entered in F1 & H1 will be (numbers, text, logicals)