CODEWITHSUNDEEP
vbaexcelif-statement
Hosey93
Hosey93

Reputation: 47

Excel if statement shorten down?

I have VBA code written to color cells based on what value is in a different cell. Would it be possible to shorten the following down? I'm new to scripting in VBA and was hoping that if this can be shortened then I could apply it to a bigger file.

 Sub RoutingCheck()
   Dim I As Long, r1 As Range, r2 As Range

   For I = 2 To 456
      Set r1 = Range("A" & I)
      Set r2 = Range("B" & I)

    If r1.Value = 94 And r2.Value = " " Then r1.Interior.Color = vbRed
    If r1.Value = 94 And r2.Value = "" Then r1.Interior.Color = vbRed
    If r1.Value = 94 And r2.Value = -99 Then r1.Interior.Color = vbRed
    If r1.Value = 94 And r2.Value = -66 Then r1.Interior.Color = vbRed
    If r1.Value = 94 And r2.Value = -77 Then r1.Interior.Color = vbRed
    If r1.Value = 94 And r2.Value = " " Then r2.Interior.Color = vbRed
    If r1.Value = 94 And r2.Value = "" Then r2.Interior.Color = vbRed
    If r1.Value = 94 And r2.Value = -99 Then r2.Interior.Color = vbRed
    If r1.Value = 94 And r2.Value = -66 Then r2.Interior.Color = vbRed
    If r1.Value = 94 And r2.Value = -77 Then r2.Interior.Color = vbRed
    If r1.Value = 94 And r2.Value <> "" And r2.Value <> -99 And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> " " Then r1.Interior.Color = vbGreen
    If r1.Value = 94 And r2.Value <> "" And r2.Value <> -99 And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> " " Then r2.Interior.Color = vbGreen

    If r1.Value = 1 And r2.Value = "" Then r1.Interior.Color = vbRed
    If r1.Value = 1 And r2.Value = -66 Then r1.Interior.Color = vbRed
    If r1.Value = 1 And r2.Value = 0 Then r1.Interior.Color = vbRed
    If r1.Value = 1 And r2.Value = -77 Then r1.Interior.Color = vbRed
    If r1.Value = 1 And r2.Value = "" Then r2.Interior.Color = vbRed
    If r1.Value = 1 And r2.Value = -66 Then r2.Interior.Color = vbRed
    If r1.Value = 1 And r2.Value = 0 Then r2.Interior.Color = vbRed
    If r1.Value = 1 And r2.Value = -77 Then r2.Interior.Color = vbRed
    If r1.Value = 1 And r2.Value <> "" And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> 0 And r2.Value <> -99 Then r1.Interior.Color = vbRed
    If r1.Value = 1 And r2.Value <> "" And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> 0 And r2.Value <> -99 Then r2.Interior.Color = vbRed
    If r1.Value = 1 And r2.Value = -99 Then r1.Interior.Color = vbGreen
    If r1.Value = 1 And r2.Value = -99 Then r2.Interior.Color = vbGreen

    If r1.Value = 2 And r2.Value = "" Then r1.Interior.Color = vbRed
    If r1.Value = 2 And r2.Value = -66 Then r1.Interior.Color = vbRed
    If r1.Value = 2 And r2.Value = 0 Then r1.Interior.Color = vbRed
    If r1.Value = 2 And r2.Value = -77 Then r1.Interior.Color = vbRed
    If r1.Value = 2 And r2.Value = "" Then r2.Interior.Color = vbRed
    If r1.Value = 2 And r2.Value = -66 Then r2.Interior.Color = vbRed
    If r1.Value = 2 And r2.Value = 0 Then r2.Interior.Color = vbRed
    If r1.Value = 2 And r2.Value = -77 Then r2.Interior.Color = vbRed
    If r1.Value = 2 And r2.Value <> "" And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> 0 And r2.Value <> -99 Then r1.Interior.Color = vbRed
    If r1.Value = 2 And r2.Value <> "" And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> 0 And r2.Value <> -99 Then r2.Interior.Color = vbRed
    If r1.Value = 2 And r2.Value = -99 Then r1.Interior.Color = vbGreen
    If r1.Value = 2 And r2.Value = -99 Then r2.Interior.Color = vbGreen

    If r1.Value = 3 And r2.Value = "" Then r1.Interior.Color = vbRed
    If r1.Value = 3 And r2.Value = -66 Then r1.Interior.Color = vbRed
    If r1.Value = 3 And r2.Value = 0 Then r1.Interior.Color = vbRed
    If r1.Value = 3 And r2.Value = -77 Then r1.Interior.Color = vbRed
    If r1.Value = 3 And r2.Value = "" Then r2.Interior.Color = vbRed
    If r1.Value = 3 And r2.Value = -66 Then r2.Interior.Color = vbRed
    If r1.Value = 3 And r2.Value = 0 Then r2.Interior.Color = vbRed
    If r1.Value = 3 And r2.Value = -77 Then r2.Interior.Color = vbRed
    If r1.Value = 3 And r2.Value <> "" And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> 0 And r2.Value <> -99 Then r1.Interior.Color = vbRed
    If r1.Value = 3 And r2.Value <> "" And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> 0 And r2.Value <> -99 Then r2.Interior.Color = vbRed
    If r1.Value = 3 And r2.Value = -99 Then r1.Interior.Color = vbGreen
    If r1.Value = 3 And r2.Value = -99 Then r2.Interior.Color = vbGreen

    If r1.Value = 4 And r2.Value = "" Then r1.Interior.Color = vbRed
    If r1.Value = 4 And r2.Value = -66 Then r1.Interior.Color = vbRed
    If r1.Value = 4 And r2.Value = 0 Then r1.Interior.Color = vbRed
    If r1.Value = 4 And r2.Value = -77 Then r1.Interior.Color = vbRed
    If r1.Value = 4 And r2.Value = "" Then r2.Interior.Color = vbRed
    If r1.Value = 4 And r2.Value = -66 Then r2.Interior.Color = vbRed
    If r1.Value = 4 And r2.Value = 0 Then r2.Interior.Color = vbRed
    If r1.Value = 4 And r2.Value = -77 Then r2.Interior.Color = vbRed
    If r1.Value = 4 And r2.Value <> "" And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> 0 And r2.Value <> -99 Then r1.Interior.Color = vbRed
    If r1.Value = 4 And r2.Value <> "" And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> 0 And r2.Value <> -99 Then r2.Interior.Color = vbRed
    If r1.Value = 4 And r2.Value = -99 Then r1.Interior.Color = vbGreen
    If r1.Value = 4 And r2.Value = -99 Then r2.Interior.Color = vbGreen

    If r1.Value = 5 And r2.Value = "" Then r1.Interior.Color = vbRed
    If r1.Value = 5 And r2.Value = -66 Then r1.Interior.Color = vbRed
    If r1.Value = 5 And r2.Value = 0 Then r1.Interior.Color = vbRed
    If r1.Value = 5 And r2.Value = -77 Then r1.Interior.Color = vbRed
    If r1.Value = 5 And r2.Value = "" Then r2.Interior.Color = vbRed
    If r1.Value = 5 And r2.Value = -66 Then r2.Interior.Color = vbRed
    If r1.Value = 5 And r2.Value = 0 Then r2.Interior.Color = vbRed
    If r1.Value = 5 And r2.Value = -77 Then r2.Interior.Color = vbRed
    If r1.Value = 5 And r2.Value <> "" And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> 0 And r2.Value <> -99 Then r1.Interior.Color = vbRed
    If r1.Value = 5 And r2.Value <> "" And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> 0 And r2.Value <> -99 Then r2.Interior.Color = vbRed
    If r1.Value = 5 And r2.Value = -99 Then r1.Interior.Color = vbGreen
    If r1.Value = 5 And r2.Value = -99 Then r2.Interior.Color = vbGreen

    If r1.Value = 6 And r2.Value = "" Then r1.Interior.Color = vbRed
    If r1.Value = 6 And r2.Value = -66 Then r1.Interior.Color = vbRed
    If r1.Value = 6 And r2.Value = 0 Then r1.Interior.Color = vbRed
    If r1.Value = 6 And r2.Value = -77 Then r1.Interior.Color = vbRed
    If r1.Value = 6 And r2.Value = "" Then r2.Interior.Color = vbRed
    If r1.Value = 6 And r2.Value = -66 Then r2.Interior.Color = vbRed
    If r1.Value = 6 And r2.Value = 0 Then r2.Interior.Color = vbRed
    If r1.Value = 6 And r2.Value = -77 Then r2.Interior.Color = vbRed
    If r1.Value = 6 And r2.Value <> "" And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> 0 And r2.Value <> -99 Then r1.Interior.Color = vbRed
    If r1.Value = 6 And r2.Value <> "" And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> 0 And r2.Value <> -99 Then r2.Interior.Color = vbRed
    If r1.Value = 6 And r2.Value = -99 Then r1.Interior.Color = vbGreen
    If r1.Value = 6 And r2.Value = -99 Then r2.Interior.Color = vbGreen

    If r1.Value = 7 And r2.Value = "" Then r1.Interior.Color = vbRed
    If r1.Value = 7 And r2.Value = -66 Then r1.Interior.Color = vbRed
    If r1.Value = 7 And r2.Value = 0 Then r1.Interior.Color = vbRed
    If r1.Value = 7 And r2.Value = -77 Then r1.Interior.Color = vbRed
    If r1.Value = 7 And r2.Value = "" Then r2.Interior.Color = vbRed
    If r1.Value = 7 And r2.Value = -66 Then r2.Interior.Color = vbRed
    If r1.Value = 7 And r2.Value = 0 Then r2.Interior.Color = vbRed
    If r1.Value = 7 And r2.Value = -77 Then r2.Interior.Color = vbRed
    If r1.Value = 7 And r2.Value <> "" And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> 0 And r2.Value <> -99 Then r1.Interior.Color = vbRed
    If r1.Value = 7 And r2.Value <> "" And r2.Value <> -66 And r2.Value <> -77 And r2.Value <> 0 And r2.Value <> -99 Then r2.Interior.Color = vbRed
    If r1.Value = 7 And r2.Value = -99 Then r1.Interior.Color = vbGreen
    If r1.Value = 7 And r2.Value = -99 Then r2.Interior.Color = vbGreen

       Next I
       'Error

        End Sub

Upvotes: 0

Views: 75

Answers (1)

Rory
Rory

Reputation: 34085

Taking the first block, you might do something like this:

If r1.Value = 94 Then
   Select case r2.Value
      case " ", "", -99, -66, -77
         r1.Interior.Color = vbRed
         r2.Interior.Color = vbRed
      Case else
         r1.Interior.Color = vbGreen
         r2.Interior.Color = vbGreen
    End Select
ElseIf r1.value = 1 then
   ' repeat the select case or use a function to return the correct colour.
' repeat
End If

Your other blocks appear to basically only care if r2 is -99, in which case use green, otherwise red.

Upvotes: 2

Related Questions