What is wrong with my code?

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

Hi

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

Code:

---

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:

Code:

---

DateFormatChecking 6, "Sheet5"

---

Richard

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]

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

Lucpian

You shouldn't really use functions to alter cells.

How and where are you actually trying the function?

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]