Htykuut

Forgive the novice loop question that has been posted so many times on SO, but I can't seem to figure out what should be simple logic. Below outlines the steps of what I am trying to accomplish:

1. Loop through all cells in range AllScores


2. Look to see if Left(wsRR.Range("H32"),1) is "P" or "G"


3. If any of the cells in range AllScores have a value between 1 and 4 and #2 above is true, then the captions of Label143 and RR_Score = "Acceptable 06"


4. 
   

   If all of the values of the cells in range AllScores >= 5 then the captions of Label143 and RR_Score = the value of range wsRR.("H32") or if all of the values in each cell in Range AllScores is >= 5 and #2 above is true or false then the captions for Labels RR_Score and Label143 = wsRR.("H32").

   
   
   

       Sub ScoringUpdateAmounts()
   
   Dim aScores As Range
   
   Dim a As Integer
   
   Dim i As Long
   
   
   
   Set wb = Application.ThisWorkbook
   
   Set wsRR = wb.Sheets("RiskRating")
   
   Set wspGen = wb.Sheets("pGeneralInfo")
   
   Set aScores = wsRR.Range("AllScores")
   
   
   
   For i = 1 To 4
   
       For Each cell In aScores
   
           If cell.Value = i Then a = 0
   
       Next cell
   
   Next i
   
   
   
   For i = 5 To 8
   
       For Each cell In aScores
   
           If cell.Value = i Then a = 1
   
       Next cell
   
   Next i
   
   
   
   Select Case Left(wsRR.Range("H32"), 4)
   
       Case Is = "GOOD"
   
           If a = 0 Then
   
               RiskCalc.RR_Score.Caption = UCase("acceptable 06")
   
               RisKRating.Label143.Caption = RiskCalc.RR_Score.Caption
   
               wspGen.Range("genRR") = UCase("acceptable 06")
   
               wspGen.Range("genJHARiskRating") = UCase("acceptable 06")
   
           End If
   
           If a = 1 Then
   
               RiskCalc.RR_Score.Caption = UCase(wsRR.Range("H32"))
   
               RisKRating.Label143.Caption = UCase(wsRR.Range("H32"))
   
               wspGen.Range("genRR") = UCase(wsRR.Range("H32"))
   
               wspGen.Range("genJHARiskRating") = UCase(wsRR.Range("H32"))
   
           End If
   
   End Select
   
   
   
   Select Case Left(wsRR.Range("H32"), 5)
   
       Case Is = "PRIME"
   
           If a = 0 Then
   
               RiskCalc.RR_Score.Caption = UCase("acceptable 06")
   
               RisKRating.Label143.Caption = RiskCalc.RR_Score.Caption
   
               wspGen.Range("genRR") = UCase("acceptable 06")
   
               wspGen.Range("genJHARiskRating") = UCase("acceptable 06")
   
           End If
   
           If a = 1 Then
   
               RiskCalc.RR_Score.Caption = UCase(wsRR.Range("H32"))
   
               RisKRating.Label143.Caption = UCase(wsRR.Range("H32"))
   
               wspGen.Range("genRR") = UCase(wsRR.Range("H32"))
   
               wspGen.Range("genJHARiskRating") = UCase(wsRR.Range("H32"))
   
           End If
   
   End Select
   
   

   
   
   

   End Sub

   
   

I doubt this will fix your problem, but this is too long for a comment.

I re-structured your code as it currently stands and removed redundant/unneeded lines.
There is something funky going on in your 1-8 loop. You may need to take a step back and re-think out the logic here.

If you just want to know if the range has a value below some threshold you can use Min function to do so and ditch the loop like so

If Application.WorksheetFunction.Min(aScores) <= 4 Then

    a = 0

Else

    a = 1

End If

Either way, easier to read/follow code tends to make debugging logic-errors much, much easier

Option Explicit



Sub ScoringUpdateAmounts()



Dim wsRR As Worksheet: Set wsRR = ThisWorkbook.Sheets("RiskRating")

Dim wspGen As Worksheet: Set wspGen = ThisWorkbook.Sheets("pGeneralInfo")

Dim aScores As Range, a As Integer, MyCell As Range



Set aScores = wsRR.Range("AllScores")



For Each MyCell In aScores

    Select Case MyCell

        Case 1, 2, 3, 5

            a = 0

        Case 5, 6, 7, 8

            a = 1

    End Select

Next MyCell



If Left(wsRR.Range("H32"), 4) = "GOOD" Then

    If a = 0 Then

        RiskCalc.RR_Score.Caption = "ACCEPTABLE 06"

        RisKRating.Label143.Caption = RiskCalc.RR_Score.Caption

        wspGen.Range("genRR") = "ACCEPTABLE 06"

        wspGen.Range("genJHARiskRating") = "ACCEPTABLE 06"

    ElseIf a = 1 Then

        RiskCalc.RR_Score.Caption = UCase(wsRR.Range("H32"))

        RisKRating.Label143.Caption = UCase(wsRR.Range("H32"))

        wspGen.Range("genRR") = UCase(wsRR.Range("H32"))

        wspGen.Range("genJHARiskRating") = UCase(wsRR.Range("H32"))

    End If

End If



If Left(wsRR.Range("H32"), 5) Then

    If a = 0 Then

        RiskCalc.RR_Score.Caption = "ACCEPTABLE 06"

        RisKRating.Label143.Caption = RiskCalc.RR_Score.Caption

        wspGen.Range("genRR") = "ACCEPTABLE 06"

        wspGen.Range("genJHARiskRating") = "ACCEPTABLE 06"

    ElseIf a = 1 Then

        RiskCalc.RR_Score.Caption = UCase(wsRR.Range("H32"))

        RisKRating.Label143.Caption = UCase(wsRR.Range("H32"))

        wspGen.Range("genRR") = UCase(wsRR.Range("H32"))

        wspGen.Range("genJHARiskRating") = UCase(wsRR.Range("H32"))

    End If

End If



End Sub

This is as close as I can get since I'm pretty sure I'm not following all your logic:

Sub ScoringUpdateAmounts()



    Dim aScores As Range, wb As Workbook, wsRR As Worksheet

    Dim a As Long, wspGen As Worksheet, cell As Range

    Dim i As Long, v, numL As Long, numH As Long, rating, capt



    Set wb = ThisWorkbook

    Set wsRR = wb.Sheets("RiskRating")

    Set wspGen = wb.Sheets("pGeneralInfo")

    Set aScores = wsRR.Range("AllScores")



    For Each cell In aScores

        v = cell.Value

        If IsNumeric(v) And Len(v) > 0 Then

            If v > 0 And v <= 4 Then

                numL = numL + 1

            ElseIf v > 4 And v <= 8 Then

                numH = numH + 1

            End If

        End If

    Next cell



    rating = UCase(wsRR.Range("H32").Value)



    If rating Like "GOOD*" Or rating Like "PRIME*" Then

        If numL > 0 Then

            capt = "ACCEPTABLE 06"

        ElseIf numL = 0 And numH > 0 Then

            capt = rating

        End If

    End If



    If Len(capt) > 0 Then

        RiskCalc.RR_Score.Caption = capt

        RisKRating.Label143.Caption = capt

        wspGen.Range("genRR") = capt

        wspGen.Range("genJHARiskRating") = capt

    End If





End Sub

I liked the solution of not looping through the range and just using the Min function, and I also liked the way @TimWilliams used the rating variable, so I combined the two separate solutions with some edits for formatting of the labels and it works perfectly. Below is the code I ended up using. Thank you both for your patience and helping this novice out. Sorry I cannot check both answers you provided as a solution.

Sub LessThanFour()

    Dim aScores As Range

    Dim a As Long

    Dim i As Long, rating, capt



    Set wb = Application.ThisWorkbook

    Set wsRR = wb.Sheets("RiskRating")

    Set wspGen = wb.Sheets("pGeneralInfo")

    Set aScores = wsRR.Range("AllScores")





    If Application.WorksheetFunction.Min(aScores) <= 4 Then

        a = 0

    Else

        a = 1

    End If



    rating = UCase(wsRR.Range("H32").Value)



    If rating Like "GOOD*" Or rating Like "PRIME*" Then

        If a = 0 Then

            capt = "ACCEPTABLE 06"

        Else

            capt = rating

        End If

    End If



    If Len(capt) > 0 Then

        RiskCalc.RR_Score.Caption = capt

        RisKRating.Label143.Caption = capt

        wspGen.Range("genRR") = capt

        wspGen.Range("genJHARiskRating") = capt

    End If



    With RiskCalc.RR_Score

        .Visible = True

        Select Case Right(capt, 1)

            Case 1 To 3: .BackColor = vbRed

            Case 4 To 5: .BackColor = vbYellow

            Case 6 To 7: .BackColor = vbGreen

            Case Is >= 8

                .BackColor = RGB(0, 153, 255)

                .ForeColor = vbWhite

        End Select

        .Font.Size = 20

        .Font.Bold = True

        .TextAlign = fmTextAlignCenter

        .BorderStyle = fmBorderStyleSingle

    End With



    With RisKRating.Label143

        .Visible = True

        Select Case Right(capt, 1)

            Case 1 To 3: .BackColor = vbRed

            Case 4 To 5: .BackColor = vbYellow

            Case 6 To 7: .BackColor = vbGreen

            Case Is >= 8

                .BackColor = RGB(0, 153, 255)

                .ForeColor = vbWhite

        End Select

        .Font.Size = 16

        .Font.Bold = True

        .TextAlign = fmTextAlignCenter

        .BorderStyle = fmBorderStyleSingle

    End With



End Sub