Use "Data Validation" after data are entered

[Guest]

Hello,

I have two xls files: file1.xls and file2.xls

1. File1.xls has been filled by a user via a data base thanks to VB.NET
code. At that level, let's suppose he doesn't know our specific constraints.
2. Each column of file2.xls is associated with "Data Validation" constraints.

I would like to use "Data Validation" of file2.xls to check file1.xls

Is it possible to check by program (via VB code ) if the content of cell
"A1" of file1.xls is OK according "Data Validation" defined in the cell "A1"
of file2.xls (after file1.xls has been fully filled)?

Thank you

 

[Bernie Deitrick]

Phillippe,

It would be a whole lot easier to do the validation solely through VBA. To
start, you would need to know each column's validation requirements.

Then you could use code like this to flag the bad cells with red
backgrounds:

Sub TryNow()
Dim myCell As Range
Dim myCol As Range

Cells.Interior.ColorIndex = xlNone

For Each myCol In ActiveSheet.UsedRange.Columns
If myCol.Column = 1 Then
For Each myCell In myCol.Cells
'This is the data validation for column A
If myCell.Value > 100 Or myCell.Value < 0 Then
myCell.Interior.ColorIndex = 3
End If
Next myCell
End If
If myCol.Column = 2 Then
For Each myCell In myCol.Cells
'This is the validation for column B
If myCell.Value > 120 Or myCell.Value < -10 Then
myCell.Interior.ColorIndex = 3
End If
Next myCell
End If
Next myCol

End Sub

HTH,
Bernie
MS Excel MVP