lucpian
03-27-2008, 07:38 AM
Hi All,
I wrote a vba code to test if date cells in my worksheet is either mm/dd/yyyy or mm/dd/yy, and if not should color it red. However, it does not work. I do not know if it is because the cells are formatted. I also tried in the code to cpmpare the string length, but it still did not work when I fill in something shorter in length or a different format. here is the code:
Sub DateFormatChecking(columnname As Long)
Dim rowcount As Long
Dim R As Long, strVal
rowcount = Range("A65536").End(xlUp).Row
For R = 2 To rowcount
strVal = Sheet1.Cells(R, columnname).NumberFormat
'MsgBox (Len(strVal))
If strVal = Null Then
Sheet1.Cells(R, columnname).Interior.ColorIndex = 0
End If
If strVal <> "mm/dd/yyyy" And (Len(strVal)) <> 8 Then
MsgBox (Len(strVal))
If strVal <> "mm/dd/yy" And (Len(strVal)) <> 8 Then
Sheet1.Cells(R, columnname).Interior.ColorIndex = 3
'Else
' If strVal = "" Then
' Sheet1.Cells(R, columnname).Interior.ColorIndex = 0
'End If
End If
End If
Next
End Sub
I might be doing something wrong that I am not seeing or possibly my code is wrong, but is not giving me errors. Please, I would be very grateful if someone in this forum will help me out.
Thanks
Lucpian
I wrote a vba code to test if date cells in my worksheet is either mm/dd/yyyy or mm/dd/yy, and if not should color it red. However, it does not work. I do not know if it is because the cells are formatted. I also tried in the code to cpmpare the string length, but it still did not work when I fill in something shorter in length or a different format. here is the code:
Sub DateFormatChecking(columnname As Long)
Dim rowcount As Long
Dim R As Long, strVal
rowcount = Range("A65536").End(xlUp).Row
For R = 2 To rowcount
strVal = Sheet1.Cells(R, columnname).NumberFormat
'MsgBox (Len(strVal))
If strVal = Null Then
Sheet1.Cells(R, columnname).Interior.ColorIndex = 0
End If
If strVal <> "mm/dd/yyyy" And (Len(strVal)) <> 8 Then
MsgBox (Len(strVal))
If strVal <> "mm/dd/yy" And (Len(strVal)) <> 8 Then
Sheet1.Cells(R, columnname).Interior.ColorIndex = 3
'Else
' If strVal = "" Then
' Sheet1.Cells(R, columnname).Interior.ColorIndex = 0
'End If
End If
End If
Next
End Sub
I might be doing something wrong that I am not seeing or possibly my code is wrong, but is not giving me errors. Please, I would be very grateful if someone in this forum will help me out.
Thanks
Lucpian