Search code examples
vbaexcel-2013

Loop to check if a cell value meets a condtion


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


Solution

  • 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