What is wrong with my code?

[lucpian]

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.

[vba] 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[/vba]
Thanks

Lucpian

EDIT: Added Code Tags Tommy

[RichardSchollar]

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]

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?

[VBA]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
[/VBA]

[lucpian]

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]

Lucpian

You shouldn't really use functions to alter cells.

How and where are you actually trying the function?

[Tommy]

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".
[VBA]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[/VBA]