PDA

View Full Version : What is wrong with my code?



lucpian
02-08-2008, 07:35 AM
Hi All,

I am trying to write a vb code to validate date fields in a particular column which I call from a menu. However, I keep having error with the if statement. I have not been able figure out what is wrong. Here, is the code.

Function DateFormatChecking(columnname As Integer)
Dim rowcount
Dim R
rowcount = Range("A65536").End(xlUp).Row
For R = 1 To rowcount
strVal = Sheet1.Cells(R, columnname).Value
If (((strVal <> "") And (strVal.NumberFormat <> "mm/dd/yy")) Or ((strVal <> "") And _
(strVal.NumberFormat <> "mm/dd/yyyy"))) Then
Sheet1.Cells(R, columnname).Interior.ColorIndex = 7
End If

Next
End Function
Thanks

Lucpian

EDIT: Added Code Tags Tommy

RichardSchollar
02-08-2008, 07:50 AM
Hi

strVal is not an object so won't have properties like 'NumberFormat'. Perhaps you could use this instead:


Sub DateFormatChecking(intColNumber As Integer, strSht As String)
Dim r As Range
With Worksheets(strSht)
For Each r In .Range(.Cells(1, intColNumber), .Cells(Rows.Count, intColNumber).End(xlUp))
If Not IsEmpty(r.Value) Then
If Not (r.NumberFormat = "mm/dd/yy" Or r.NumberFormat = "mm/dd/yyyy") Then _
r.Interior.ColorIndex = 7
End If
Next r
End With
End Sub

and call it from within code like:


DateFormatChecking 6, "Sheet5"

Richard

Tommy
02-08-2008, 08:02 AM
Hi lucipan,
I got this to work, but am not sure what you are tryng to accomplish. Are coloring the background if the formatting is incorrect? or if it is correct?

Function DateFormatChecking(columnname As Long)
Dim rowcount As Long
Dim R As Long, strVal
rowcount = Range("A65536").End(xlUp).Row
For R = 1 To rowcount
strVal = Sheet1.Cells(R, columnname).NumberFormat
If strVal <> "" And strVal <> "mm/dd/yy;@" And strVal <> "mm/dd/yyyy;@" Then
Sheet1.Cells(R, columnname).Interior.ColorIndex = 7
End If
Next
End Function

lucpian
02-08-2008, 08:22 AM
Tommy,

Thanks, but I am trying to get the background color in there if the formatting is wrong. Your code works when it is correct. What should I do. I try to fiddle with your code, but went right back to the errors, again

Lucpian

Norie
02-08-2008, 08:31 AM
Lucpian

You shouldn't really use functions to alter cells.

How and where are you actually trying the function?

Tommy
02-08-2008, 09:09 AM
I agree with Norie, don't use a function that returns nothing, use a sub. :)
I have not changed the code much and it is not supposed to color if the formatting is correct. I also could not find a format for "mm/dd/yyyy" so I used "m/d/yyyy".
Sub DateFormatChecking(columnname As Long)
Dim rowcount As Long
Dim R As Long, strVal
rowcount = Range("A65536").End(xlUp).Row
For R = 1 To rowcount
strVal = Sheet1.Cells(R, columnname).NumberFormat
If Not IsEmpty(strVal) And strVal <> "mm/dd/yy;@" And strVal <> "m/d/yyyy;@" Then
Sheet1.Cells(R, columnname).Interior.ColorIndex = 7
End If
Next
End Sub