Solved: What is wrong with my Validation code?

[lucpian]

Hi,

I have jus written a code to validate the entries in column C in my worksheet which should be either Y or M. Below is the code I wrote, but I keep having error. Please, can someone help me out.


Function MaintenanceFreqChecking(columnname As Integer)

Dim rowcount

Dim R
Dim Y As String
Dim M As String
rowcount = Range("A65536").End(xlUp).Row
For R = 2 To rowcount
strVal = Sheet1.Cells(R, columnname).Value
If strVal <> "Y" Or strVal <> "M" Then
Sheet1.Cells(R, columnname).Interior.ColorIndex = 6
End If

Next

End Function

Thanks

Lucpian

[rory]

Change the Or to And:
[VBA]
If strVal <> "Y" And strVal <> "M" Then
[/VBA]

[Norie]

Or use conditional formatting.

1 Select all the rows in column C you want to check.

2 Goto Format>Conditional formatting.

3 Select formula is from the dropdown.

4 Enter this formula.

=AND(C1<>"Y", C1<>"M")

5 Format as required.

PS You do realise Excel has Data>Validation...

[MWE]

the VBA code as modified, may still not give you exactly what you want because (1) it assumes that all cell colors are set to default (no fill?) when the procedure runs and (2) it does not check for the case where the cells contain leading or trailing spaces (if that is acceptable). To solve (1), you could clear all the colors from the relevant cells first. Another approach is to check for cell value = Y or M and if so explicitly set the cell color to default (no fill?); otherwise set it to the error color. I would do the latter because the code is more obvious:
[vba]If strVal = "Y" or strVal = "M" then
Sheet1.Cells(R, columnname).Interior.ColorIndex = xlNone
Else
Sheet1.Cells(R, columnname).Interior.ColorIndex = 6
end if[/vba]
If leading or trailing spaces or blanks around the "Y" or "M" are not acceptable, e.g., " Y" is not acceptable, nor is "M ", then the above is fine. If spaces are OK, then you could use the Trim functioin to trim away any leading or trailing blanks and the code might be:
[vba]If Trim(strVal) = "Y" or strVal = "M" then
Sheet1.Cells(R, columnname).Interior.ColorIndex = xlNone
Else
Sheet1.Cells(R, columnname).Interior.ColorIndex = 6
end if[/vba]