Macro with data validation

[John P]

Is it possible to create a macro performs data validation? We would like to allow users to capture data into a worksheet, then click a button that runs a macro to perform data validation on certain fields. For example, one field may be called User Name. We expect user names to appear as Johnny, Mike and Damian. The user enters Michael, so when the data validation macro runs, it highlights the cell that has Michael entered so they know it failed validation. Of course, we would need validation performed on 4 unique columns that contain different data each column with its own rules.

Any suggestions are greatly appreciated.

John

 

[Juan Pablo González]

Why not use the builtin Data | Validation feature ?

 

[John P]

Hi Juan,
I appreciate your response. We actually started down the path with the built-in validation but the user demanded the ability to copy/paste data and when they copy/paste within a cell that has validation, the validation does not function. Due to this issue we have decided to find a method for utilizing a macro that runs AFTER they enter their data.

 

[Joe4]

Here is a small macro that should help get you started. This checks the entries in column A for name, and highlights anything that in not Johnny, Mike, or Damian yellow.

I am assuming the data begins on line 2, and we are using column A to determine where the last row falls.

Sub MyCheck()

    Application.ScreenUpdating = False

    Dim i As Integer
    
'   Loop from row 2 to end (using column A to determine last row
    For i = 2 To Range("A65536").End(xlUp).Row
'   Check value in column A for names
        Select Case Cells(i, 1).Value
            Case "Johnny", "Mike", "Damian"
'   If column A is one of pre-defined choices, do NOT highlight cell
                Cells(i, 1).Interior.ColorIndex = xlNone
'   If column A is NOT one of pre-defined choices, highlight cell
            Case Else
                Cells(i, 1).Interior.ColorIndex = 6
        End Select
    Next i
    
   Application.ScreenUpdating = True
    
End Sub

You can enter more Case Select statments (just fancy IF statements) inside the loop to handle the other columns (column 2 would be Cells(i,2)).

 

[BigJohnV]

I know that this example was posted a long time ago, but how would I go about changing it so as the list of valid values is extracted from a list of values that have been entered on a separate tab in the same workbook? Many thanks