Sub Eligibilitycheck()
Dim lastrow As Integer
Dim i As Integer
lastrow = Range("A65536").End(xlUp).Row
For i = lastrow To 2 Step -1
'height
If Cells(i, 9).Value = "0" Then
Cells(i, 9).Value = ""
ElseIf Cells(i, 9).Value < 24 Then
Cells(i, 27).Value = Cells(i, 27).Value & "Height - Out of range"
ElseIf Cells(i, 9).Value > 90 Then
Cells(i, 27).Value = Cells(i, 27).Value & "Height - Out of range"
End If
'weight
If Cells(i, 10).Value = "0" Then
Cells(i, 10).Value = ""
ElseIf Cells(i, 10).Value < 50 Then
Cells(i, 27).Value = Cells(i, 27).Value & "Weight - Out of range"
ElseIf Cells(i, 10).Value > 600 Then
Cells(i, 27).Value = Cells(i, 27).Value & "Weight - Out of range"
End If
'waist circ/bmi
If Cells(i, 13).Value = "" And Cells(i, 11).Value = "" Then
Cells(i, 27).Value = Cells(i, 27).Value & "WaistC/BMI - Missing"
If Cells(i, 13).Value = "0" Then
Cells(i, 13).Value = ""
ElseIf Cells(i, 13).Value < 15 Then
Cells(i, 27).Value = Cells(i, 27).Value & "WaistC - Out of range"
ElseIf Cells(i, 13).Value > 70 Then
Cells(i, 27).Value = Cells(i, 27).Value & "WaistC - Out of range"
End If
If Cells(i, 11).Value = "0" Then
Cells(i, 11).Value = ""
ElseIf Cells(i, 11).Value < 10 Then
Cells(i, 27).Value = Cells(i, 27).Value & "BMI - Out of range"
ElseIf Cells(i, 11).Value > 70 Then
Cells(i, 27).Value = Cells(i, 27).Value & "BMI - Out of range"
End If
'HDL
If Cells(i, 15).Value = "0" Then
Cells(i, 15).Value = ""
ElseIf Cells(i, 15).Value < 10 Then
Cells(i, 27).Value = Cells(i, 27).Value & "HDL - Out of range"
ElseIf Cells(i, 15).Value > 170 Then
Cells(i, 27).Value = Cells(i, 27).Value & "HDL - Out of range"
End If
'LDL
If Cells(i, 16).Value = "0" Then
Cells(i, 16).Value = ""
ElseIf Cells(i, 16).Value < 20 Then
Cells(i, 27).Value = Cells(i, 27).Value & "LDL - Out of range"
ElseIf Cells(i, 16).Value > 300 Then
Cells(i, 27).Value = Cells(i, 27).Value & "LDL - Out of range"
End If
'triglycerides
If Cells(i, 17).Value = "0" Then
Cells(i, 17).Value = ""
ElseIf Cells(i, 17).Value < 20 Then
Cells(i, 27).Value = Cells(i, 27).Value & "Tryg - Out of range"
ElseIf Cells(i, 17).Value > 7000 Then
Cells(i, 27).Value = Cells(i, 27).Value & "Tryg - Out of range"
End If
'glucose
If Cells(i, 18).Value = "0" Then
Cells(i, 18).Value = ""
ElseIf Cells(i, 18).Value < 50 Then
Cells(i, 27).Value = Cells(i, 27).Value & "glucose - Out of range"
ElseIf Cells(i, 18).Value > 500 Then
Cells(i, 27).Value = Cells(i, 27).Value & "glucose - Out of range"
End If
'a1c
If Cells(i, 19).Value = "0" Then
Cells(i, 19).Value = ""
ElseIf Cells(i, 19).Value < 4 Then
Cells(i, 27).Value = Cells(i, 27).Value & "hbA1c - Out of range"
ElseIf Cells(i, 19).Value > 15 Then
Cells(i, 27).Value = Cells(i, 27).Value & "hbA1c - Out of range"
End If
'systolic bp
If Cells(i, 20).Value = "0" Then
Cells(i, 20).Value = ""
ElseIf Cells(i, 20).Value < 70 Then
Cells(i, 27).Value = Cells(i, 27).Value & "Systolic BP - Out of range"
ElseIf Cells(i, 20).Value > 250 Then
Cells(i, 27).Value = Cells(i, 27).Value & "Systolic BP - Out of range"
End If
'Diastolic bp
If Cells(i, 21).Value = "0" Then
Cells(i, 21).Value = ""
ElseIf Cells(i, 21).Value < 40 Then
Cells(i, 27).Value = Cells(i, 27).Value & "Diastolic BP - Out of range"
ElseIf Cells(i, 21).Value > 150 Then
Cells(i, 27).Value = Cells(i, 27).Value & "Diastolic BP - Out of range"
End If
'Dia > Sys?
If Cells(i, 21).Value > Cells(i, 20).Value Then Cells(i, 27).Value = Cells(i, 27).Value & "Diastolic greater than Systolic"
End If
Next i
End Sub
Bookmarks