patternMinor
Else if .value = 1 to 92 code check
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 SubSolution
You can only use the
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.
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 SubCode 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 SubContext
StackExchange Code Review Q#153661, answer score: 4
Revisions (0)
No revisions yet.