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:
Left(wsRR.Range("H32"),1)
is "P" or "G"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 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