Ratio Conditional Requirements for VBA Excel

Question

I have a financial data that indicates the health of the project and depending on the variance it populates a color and a letter to indicate the health. For example, if the varinace is less than 5% it populates color green with letter "G", >5% but less than 20% populates "Y" and >20% = "R". I've used the if statements to get that. however I need to populate a color of overall projects.

There are 5 projects and these are the requirements:

1. If any of the 5 projects have "R" then the overall health is "R"
2. If 1/5 project is "Y" and 4/5 = "G" then the overall health is "G"
3. If 2/5 = "Y" and 3/5 "G" then the overall health is "Y"
4. If 3/5 = "Y"and 2/5 "G" then overall health is "Y"
5. If 4/5 = "Y" and 1/5 "G" Then "Y"
6. If 5/5 = "Y" then "Y"
7. If 5/5 = "G" then "G"

So, if 1st project is G, 2nd project is Y 3rd project is Y, 4 Project is G and 5th project is R I want it to popoulate "R" for overall health.

I've tried using if statements but the amount of combination i have to do is too much.

How do i use VBA excel to create this kind of "ratio" requirements?

Thank you

Answer 1

You can create a variable for that counts each Y, R, and G and then do a simple if elseif to set a cell equal to r, y, or g based on your rules:

Option Explicit

Sub getcounts()

Dim g As Long
Dim r As Long
Dim y As Long
Dim ovrallhealth As Range
Dim variancerange As Range
Set variancerange = Sheets(1).Range("A1:A5")
Set ovrallhealth = Sheets(1).Range("B1")

y = Application.WorksheetFunction.CountIf(variancerange, "y")
g = Application.WorksheetFunction.CountIf(variancerange, "g")
r = Application.WorksheetFunction.CountIf(variancerange, "r")

If r > 0 Then
   ovrallhealth = "R"
ElseIf y = 1 And g = 4 Then
   ovrallhealth = "G"
ElseIf y = 2 And g = 3 Then
   ovrallhealth = "Y"
ElseIf y = 3 And g = 2 Then
   ovrallhealth = "Y"
ElseIf y = 4 And g = 1 Then
   ovrallhealth = "Y"
ElseIf y = 5 Then
   ovrallhealth = "Y"
ElseIf g = 5 Then
   ovrallhealth = "G"
End If

End Sub