HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Else if .value = 1 to 92 code check

Submitted by: @import:stackexchange-codereview··
0
Viewed 0 times
codecheckvalueelse

Problem

I've written code that selects column D and checks it for codes 1 to 92 however I have to write out c4.Value = 1 Or c4.Value = 2 all the way up to c4.Value = 92, is there a quicker way of doing this? Please see below for my code and I appreciate all help.

Sub q2country_and_q2country_other()

   Dim i As Long, c4 As Range, c5 As Range

   For i = 2 To 456
      Set c4 = Range("D" & i)
      Set c5 = Range("E" & i)

  If c4.Value = 94 Then
      Select Case c5.Value
      Case "", " ", "0", -99, -66, -77
         c4.Interior.color = vbRed
         c5.Interior.color = vbRed
      Case Else
         c4.Interior.color = vbGreen
         c5.Interior.color = vbGreen
   End Select

  ElseIf c4.Value = 1 Or c4.Value = 92 Then
      Select Case c5.Value
      Case -99, ""
         c4.Interior.color = vbGreen
         c5.Interior.color = vbGreen
      Case Else
         c4.Interior.color = vbRed
         c5.Interior.color = vbRed
      End Select

End If
Next i

End Sub

Solution

You can only use the To syntax with Case statements inside a Select block, and you can nest Select Blocks....

Note that indentation helps enormously, so I've changed the indentation to 2 spaces, although 4 is more typical, but doesn't always suit StackExchange rendering.

Sub q2country_and_q2country_other()

   Dim i As Long, c4 As Range, c5 As Range

   For i = 2 To 456
      Set c4 = Range("D" & i)
      Set c5 = Range("E" & i)

      Select Case c4.Value
        Case 94 
          Select Case c5.Value
            Case "", " ", "0", -99, -66, -77
              c4.Interior.color = vbRed
              c5.Interior.color = vbRed
            Case Else
              c4.Interior.color = vbGreen
              c5.Interior.color = vbGreen
          End Select

        Case 1 To 92 
          Select Case c5.Value
            Case -99, ""
              c4.Interior.color = vbGreen
              c5.Interior.color = vbGreen
            Case Else
              c4.Interior.color = vbRed
              c5.Interior.color = vbRed
          End Select
      End Select
    Next i
End Sub

Code Snippets

Sub q2country_and_q2country_other()

   Dim i As Long, c4 As Range, c5 As Range

   For i = 2 To 456
      Set c4 = Range("D" & i)
      Set c5 = Range("E" & i)

      Select Case c4.Value
        Case 94 
          Select Case c5.Value
            Case "", " ", "0", -99, -66, -77
              c4.Interior.color = vbRed
              c5.Interior.color = vbRed
            Case Else
              c4.Interior.color = vbGreen
              c5.Interior.color = vbGreen
          End Select

        Case 1 To 92 
          Select Case c5.Value
            Case -99, ""
              c4.Interior.color = vbGreen
              c5.Interior.color = vbGreen
            Case Else
              c4.Interior.color = vbRed
              c5.Interior.color = vbRed
          End Select
      End Select
    Next i
End Sub

Context

StackExchange Code Review Q#153661, answer score: 4

Revisions (0)

No revisions yet.