Can't think my way thru the logic

[jim may]

I have some dates and integers Row 3) across several columns -- The below code in "Worksheet_Change" code. If my target value is say 20
I need for the Msgbox to appear with the statement "You have entered a Weekend Date". What am I not doing right? The Range Name "VALIDDAYS" is a joined range G3:K3, M3:Q3 and the For Each is properly reading each one correctly

Excel 2012

	G	H	I	J	K	L	M	N
1	15-Sep	16-Sep	17-Sep	18-Sep	19-Sep	X	22-Sep	23-Sep
2	Mon	Tue	Wed	Thu	Fri		Mon	Tue
3	15	16	17	18	19		22	23

<COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5"><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Cash Proj​

.....
For Each C In Range("ValidDays").Cells
If C.Value <> Target.Value Then
'Do nothing
Else
MsgBox "You have entered a Weekend Date"
Application.Undo
Exit For
End If
Next C
[/code}

 

[shg]

Isn't all of the date entry done in row 1? Rows 2 and 3 are just the same date formatted differently.

 

[skywriter]

Where is the target value of 20 coming from? Is this when you type a date in the first row?
If so, wouldn't you need to extract the day from the date you entered to get 20?

 

[Andrew Poulsom]

In what way isn't your VBA code working?

 

[Caribeiro77]

..

 

[jim may]

Thanks for looking Shg,,

This is a monthly budget spread across umpteen columns

In my Column D (not shown) I enter on any row between (5 and 70) a number for the day of the month I wish to pay a given amt (in Cell E33); So like for example in Cell D33 - the enter the number 20; I want my macro to search my row 3 range and if it doesn't find my target (20), it delivers the message "THE 20th is a Weekend Date - Try Again!!").

I'm brain dead on this one...

Thanks,

Jim

 

[shg]

Why not just use data validation? =WEEKDAY(entrycell, 2) <=5

 

[jim may]

Ok Guys, I tried this.. (it seems to work) Comments????

With Range("ValidDays").Cells
    Set R = .Find(Target.Value)
    
End With
If R Is Nothing Then
        MsgBox "You have entered a Weekend Date"
        Application.Undo
End If

 

[shg]

It seems like overkill to use VBA for this.

 

[jim may]

I ended up doing this, and it seems to work FINE..

CurrYr = Year(Range("G1"))
CurrMo = Month(Range("G1"))
CurrDy = Day(Range("G1"))
If CurrDy < Target.Value Then
Paydate = DateSerial(CurrYr, CurrMo, Target)
Else
Paydate = DateSerial(CurrYr, CurrMo + 1, Target)
End If

If Weekday(Paydate, 2) = 6 Or Weekday(Paydate, 2) = 7 Then
        MsgBox "You have entered a Weekend Date"
        Application.Undo

Thanks for your input. Jim