Users will enter in Column B the order date. In Column C they will enter the
expected ship date. If the expected ship date is less than 8 weeks (not
including weekends), I would like a message to pop up. I'm using the formula
=IF(B7="","",INT((C7-MOD(C7-O7,7)-B7+7)/7)) (O7 contains the value indicating
that Sunday is the first day of the week). Is this possible with data
validation or do I need to use a message box? If I need a message box, I am
not very proficient in VBA so would need some guidance on that. Thanks so
much!
--
maryj

You may consider a third option, conditional formating. Data validation will
prevent the data to be entered, while conditional formating will allow the
data entered, but show a warning (like red fill, for example)
In both cases you can use formulas that evaluate to TRUE or FALSE, with the
"Custom" criteria in Data validation, and the "Formula Is" in conditional
formating.

Hope this helps,
Miguel.

"maryj" wrote:

Users will enter in Column B the order date. In Column C they will enter the
expected ship date. If the expected ship date is less than 8 weeks (not
including weekends), I would like a message to pop up. I'm using the formula
=IF(B7="","",INT((C7-MOD(C7-O7,7)-B7+7)/7)) (O7 contains the value indicating
that Sunday is the first day of the week). Is this possible with data
validation or do I need to use a message box? If I need a message box, I am
not very proficient in VBA so would need some guidance on that. Thanks so
much!
--
maryj

I was thinking I would use conditional formatting as well but would really
like a box to appear immediately when the dates are entered.
--
maryj


"Miguel Zapico" wrote:

You may consider a third option, conditional formating. Data validation will
prevent the data to be entered, while conditional formating will allow the
data entered, but show a warning (like red fill, for example)
In both cases you can use formulas that evaluate to TRUE or FALSE, with the
"Custom" criteria in Data validation, and the "Formula Is" in conditional
formating.

Hope this helps,
Miguel.

"maryj" wrote:

Users will enter in Column B the order date. In Column C they will enter the
expected ship date. If the expected ship date is less than 8 weeks (not
including weekends), I would like a message to pop up. I'm using the formula
=IF(B7="","",INT((C7-MOD(C7-O7,7)-B7+7)/7)) (O7 contains the value indicating
that Sunday is the first day of the week). Is this possible with data
validation or do I need to use a message box? If I need a message box, I am
not very proficient in VBA so would need some guidance on that. Thanks so
much!
--
maryj

One workaround without macros may be crafting a message-box like cell, and
apply conditional format to that cell depending on the values of the entry
cell. For example, white ink over no fill as default, red ink, big bold red
letters, gray fill if the condition is met.
Validation will do pop up a box, with the side effect of not allowing the
value.

And if you want to do it with macros, I would use the Worksheet_change
event, checking the cell address, the contents, and displaying the message if
needed. For example :
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
'Act only on column C changes
If Target.Column = 3 Then
'This variable section should be more descriptive
Dim cellO, cellB, cellC
cellO = Target.Offset(0, 12).Value
cellB = Target.Offset(0, -1).Value
cellC = Target.Value
'This is the condition check
If ((cellC - (cellC - cellO) Mod 7) - cellB + 7) \ 7 8 Then
MsgBox "More than 8 weeks", vbCritical, "Warning"
End If
End If
End Sub

This code has some hardcoded references (like the offset numbers) so it may
be difficult to maintain if you don't feel comfortable with VBA.

Miguel.

"maryj" wrote:

I was thinking I would use conditional formatting as well but would really
like a box to appear immediately when the dates are entered.
--
maryj


"Miguel Zapico" wrote:

You may consider a third option, conditional formating. Data validation will
prevent the data to be entered, while conditional formating will allow the
data entered, but show a warning (like red fill, for example)
In both cases you can use formulas that evaluate to TRUE or FALSE, with the
"Custom" criteria in Data validation, and the "Formula Is" in conditional
formating.

Hope this helps,
Miguel.

"maryj" wrote:

Users will enter in Column B the order date. In Column C they will enter the
expected ship date. If the expected ship date is less than 8 weeks (not
including weekends), I would like a message to pop up. I'm using the formula
=IF(B7="","",INT((C7-MOD(C7-O7,7)-B7+7)/7)) (O7 contains the value indicating
that Sunday is the first day of the week). Is this possible with data
validation or do I need to use a message box? If I need a message box, I am
not very proficient in VBA so would need some guidance on that. Thanks so
much!
--
maryj