Macro with data validation

John P

Board Regular
Joined
Oct 17, 2002
Messages
108
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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.
 
Upvote 0
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.

Code:
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)).
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top