VBA Validation on Copy/Paste

[reshi]

Hello, I am new to ExcelForum and relatively new to VBA.

I am trying to validate multiple columns in a sheet that have multiple validation rules.

- Columns A and B need to only allow alpha values
- Column E needs to only allow the letter "X" or "x" or simply a blank
- Columns H and J must only allow positive whole numbers between 1 and 1000.


**I also need to ENSURE that these data validation rules stick even if someone copies and pastes data onto the sheet.

***If someone copies and pastes bad data, those cells should be highlighted.

Any help would be appreciated. Thanks

[stnkynts]

What do you mean by "alpha values"

Edit: I am going to assume you mean alphanumeric

[reshi]

strictly alphabetic. no numbers.

[reshi]

strictly alphabetic. no numbers.

can you provide some advice to my problem?

[stnkynts]

This should cover all the parameters you specified

Sub Validate_Special()
Dim ws As Worksheet:    Set ws = Sheets("Sheet1")
Dim objRegExp As Object
Dim regExp_Matches
Dim c As Range, rng As Range
Dim LR As Long

Set objRegExp = CreateObject("vbscript.regexp")
objRegExp.Global = True
objRegExp.Pattern = "[^a-z^A-Z]"

LR = ws.UsedRange.Rows.Count + 1
Set rng = Union(ws.Range("A1:B" & LR), ws.Range("E1:E" & LR), ws.Range("H1:H" & LR), ws.Range("J1:J" & LR))

For Each c In rng
    If Len(c) > 0 Then
        Select Case c.Column
            Case 1 To 2
                Set regExp_Matches = objRegExp.Execute(c.Value)
                If regExp_Matches.Count <> 0 Then
                    'string not alpha
                    c.Interior.Color = vbYellow
                End If
            Case 5
                If c <> "X" And c <> "x" And Len(c) <> 0 Then
                    c.Interior.Color = vbYellow
                End If
            Case 8, 10
                If IsNumeric(c) Then
                    If CDbl(c) - CInt(c) <> 0 Then
                        c.Interior.Color = vbYellow
                    End If
                Else
                    c.Interior.Color = vbYellow
                End If
        End Select
    End If
Next c

End Sub

[reshi]

My workbook is macro-enabled, but the code isn't stimulating anything? Am I supposed to place this within Worksheet_Change or just General?

[reshi]

Also, thank you so much for helping out. I really really appreciate it.

[reshi]

just kidding, I figured it out.

I just entered "1.2" into a cell in Columns A and B. The code you created currently highlights ALL entered values regardless if they're incorrect or not. I just want those values that are incorrect to be highlighted.

[reshi]

Just kidding again! I figured it all out. Thank you so so much

[stnkynts]

I am glad to hear everything worked out.