edit this code so it checks each cell has a number between 1 & 999?

[tonywatsonhelp]

Hi Everyone,

I got this code yesterday that works great,

it checks if a cell is empty or if it has 6 numbers but i need to change the second criteria?

I need it to check that every cell is a number between 1-999
but hears the problem they can have 1 letter at the end so 1A to 999Z is fine but so is just 1 to 999.

if anyone can help this would be great

Thanks

Tony

heres the code i have

Sub Col_E_CheckForSixDIgitNumbers()
  Dim Cell As Range, Yellow As Long, Red As Long, Msg As String
  For Each Cell In Data1.Range("E15:E" & Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row)
    If Cell.Value = "" Then
      Cell.Interior.Color = RGB(255, 255, 0) 'yellow
      Cell.Font.Color = RGB(117, 71, 78)
      Yellow = 1

    ElseIf Cell.Value Like "######" Then 'this is the bit that needs changing?
      Cell.Interior.Color = RGB(235, 245, 230) 'light green
      Cell.Font.Color = RGB(117, 71, 78)
    Else


      Cell.Interior.Color = RGB(255, 0, 0) 'red
      Cell.Font.Color = RGB(255, 255, 255) 'white
      Red = 1
    End If
  Next
  If Yellow + Red = 2 Then
    MsgBox "You have some incorrect inputs and blank cells in the ""receipt"" column please fix each red and each yellow cell before proceeding!", , "Column E Has Errors!"
  ElseIf Yellow Then
    MsgBox """receipt"" column has missing data, please fix each yellow cell before proceeding!", , "Column E Has Errors!"
  ElseIf Red Then
    MsgBox "You have some incorrect inputs in the ""receipt"" column please fix each red cell before proceeding!", , "Column E Has Errors!"
  End If
End Sub

 

[fadee2]

try

 ElseIf IsNumeric(cell.value) = True And cell.value>= 1 And cell.value <= 999 Then 
      Cell.Interior.Color = RGB(235, 245, 230) 'light green
      Cell.Font.Color = RGB(117, 71, 78)
    Else

The code will check whether the value is numeric or not, then if numeric value exists in between 1 and 999 and will perform your coloring tasks, when all three conditions are true.

hth....

 

[tonywatsonhelp]

Thanks fadee but i dont see how this deal with all the checks?
if cell is 1 to 999 then great
but what if it has a letter on the end?
like 999S ? or 99SD ?

 

[DanteAmor]

Try this:

Sub Col_E_CheckForSixDIgitNumbers()
  Dim Cell As Range, Yellow As Long, Red As Long, Msg As String
  Dim xGreen As Boolean, xRed As Boolean, xYellow As Boolean
  For Each Cell In Data1.Range("E15:E" & Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row)
    xGreen = False
    xRed = False
    xYellow = False
    
    Select Case True
      Case Cell.Value = ""
        xYellow = True
      Case Right(Cell.Value, 1) Like "*[A-Za-z0-9]*"
        If Right(Cell.Value, 1) Like "*[A-Za-z]*" Then
          If Mid(Cell.Value, 1, Len(Cell.Value) - 1) >= 1 And _
            Mid(Cell.Value, 1, Len(Cell.Value) - 1) <= 999 Then
            xGreen = True
          Else
            xRed = True
          End If
        ElseIf Cell.Value >= 1 And Cell.Value <= 999 Then
          xGreen = True
        Else
          xRed = True
        End If
      Case Else
        xRed = True
    End Select
    
    If xGreen Then
      Cell.Interior.Color = RGB(235, 245, 230) 'light green
      Cell.Font.Color = RGB(117, 71, 78)
    ElseIf xRed Then
      Cell.Interior.Color = RGB(255, 0, 0) 'red
      Cell.Font.Color = RGB(255, 255, 255) 'white
      Red = 1
    ElseIf xYellow Then
      Cell.Interior.Color = RGB(255, 255, 0) 'yellow
      Cell.Font.Color = RGB(117, 71, 78)
      Yellow = 1
    End If
  Next
  If Yellow + Red = 2 Then
    MsgBox "You have some incorrect inputs and blank cells in the ""receipt"" column please fix each red and each yellow cell before proceeding!", , "Column E Has Errors!"
  ElseIf Yellow Then
    MsgBox """receipt"" column has missing data, please fix each yellow cell before proceeding!", , "Column E Has Errors!"
  ElseIf Red Then
    MsgBox "You have some incorrect inputs in the ""receipt"" column please fix each red cell before proceeding!", , "Column E Has Errors!"
  End If
End Sub

 

[Marc L]

​

Or just using the Val VBA function …​

 

[fadee2]

Thanks fadee but i dont see how this deal with all the checks?
if cell is 1 to 999 then great
but what if it has a letter on the end?
like 999S ? or 99SD ?

Well the code will first check whether the value is numerical or not, then will proceed to check if the numerical value exists in between 1 and 999, and so on....
But if the value is a non-numerical number, e.g. 999S or A999 then the code will skip that cell...

hth...

 

[DanteAmor]

like 999S ? or 99SD ?

1 or 2 letters or several?
always at the end or can they also be at the beginning?

 

[Marc L]

Dante,​

​

the number no matters if letters are at the end of the string just using Val function …​