Looking for an error in VBA-Excel Data validation code-CodePudding

I am currently working on a entry data checking vba-code and this is what i got so far. The code is supposed to mark values that are out of range and throw an error when the wrong datatype is entered. Right now the code throws an error for every entry in the table even if it is in range. I do not find the problem though. Does anyone now where I made a mistake?

Sub CheckColumns()
Dim rng As Range
Dim lCol As Long, lRow As Long
Dim DblWidthMax As Double
Dim DblHeightMax As Double
Dim DblLengthMin As Double
Dim DblWidthMin As Double
Dim DblHeightMin As Double
Dim DblLengthMax As Double


DblLenghtMax = 20000
DblWidthMax = 20000
DblHeightMax = 20000
DblLengthMin = 5
DblWidthMin = 5
DblHeightMin = 5

    lCol = Range("C2").End(xlToRight).Column
    lRow = Range("C2").End(xlDown).Row

    For Each rng In Range("C2", Cells(lRow, lCol))
        If IsNumeric(rng) = False Then
          MsgBox ("A number has to be entered " & "Row " & rng.Row 
          & " Column " & rng.Column)
          rng.Font.ColorIndex = 3
    End If

       If rng.Value > DblLengthMax Or rng.Value < DblLengthMin Then
         MsgBox ("Value in " & "Row " & rng.Row & " Column " & 
         rng.Column & " is out of range. Check for unit (mm)")
         rng.Font.ColorIndex = 46
        
      End If

    Next rng

End Sub

So for example I enter the values: 3000, 50, 3. for length width and height. Since my range is 5 to 20000 the first two values should be in Range and not be colored. Right now every value is colored orange. I would only expect the 3 to be coloured

CodePudding user response：

Your variables have not been properly declared. As example, you have

Dim DblLengthMax As Double

But then you do:

DblLenghtMax = 20000

Notice is not the same variable!!!! So DblLengthMax is initialized as 0. Then your code does:

If rng.Value > DblLengthMax Or rng.Value < DblLengthMin Then but remember because of your typo DblLengthMax=0 so any value greater than 0 will be colored...

ALWAYS use Option Explicit command to avoid these errors and typos: