Hi Jonathan
The spreadsheet looks like this(sample)
Name Phase1 Phase2 Phase3 Phase4 Phase 5 Phase6 Phase7 Completed
John 50%100% 100% 100% 100% 100% 100%
Denis100%100% 100% 100% 100% 100% 100%
the macro has the following code
Sub percentage()
rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
Range("i2:I" & rowcount).ClearContents
For i = 2 To rowcount
Range("a" & i).Select
Selection.Offset(0, 1).Select
phase1 = ActiveCell.Value
If phase1 < 1 Then
rowcount1 = Cells(Cells.Rows.Count, "i").End(xlUp).Row
Range("I" & rowcount1 + 1).Select
ActiveCell.Value = "Phase 1"
GoTo line1:
End If
Selection.Offset(0, 1).Select
phase2 = ActiveCell.Value
If phase2 < 1 Then
rowcount1 = Cells(Cells.Rows.Count, "i").End(xlUp).Row
Range("I" & rowcount1 + 1).Select
ActiveCell.Value = "Phase 2"
GoTo line1
End If
Selection.Offset(0, 1).Select
phase3 = ActiveCell.Value
If phase3 < 1 Then
rowcount1 = Cells(Cells.Rows.Count, "i").End(xlUp).Row
Range("I" & rowcount1 + 1).Select
ActiveCell.Value = "Phase 3"
GoTo line1
End If
Selection.Offset(0, 1).Select
phase4 = ActiveCell.Value
If phase4 < 1 Then
rowcount1 = Cells(Cells.Rows.Count, "i").End(xlUp).Row
Range("I" & rowcount1 + 1).Select
ActiveCell.Value = "Phase 4"
GoTo line1
End If
Selection.Offset(0, 1).Select
phase5 = ActiveCell.Value
If phase5 < 1 Then
rowcount1 = Cells(Cells.Rows.Count, "i").End(xlUp).Row
Range("I" & rowcount1 + 1).Select
ActiveCell.Value = "Phase 5"
GoTo line1
End If
Selection.Offset(0, 1).Select
phase6 = ActiveCell.Value
If phase6 < 1 Then
rowcount1 = Cells(Cells.Rows.Count, "i").End(xlUp).Row
Range("I" & rowcount1 + 1).Select
ActiveCell.Value = "Phase 6"
GoTo line1
End If
Selection.Offset(0, 1).Select
phase7 = ActiveCell.Value
If phase7 < 1 Then
rowcount1 = Cells(Cells.Rows.Count, "i").End(xlUp).Row
Range("I" & rowcount1 + 1).Select
ActiveCell.Value = "Phase 7"
GoTo line1
End If
If phase1 = 1 And phase2 = 1 And phase3 = 1 And phase4 = 1 And phase5 =
1 And phase6 = 1 And phase7 = 1 Then
rowcount1 = Cells(Cells.Rows.Count, "i").End(xlUp).Row
Range("I" & rowcount1 + 1).Select
ActiveCell.Value = "Completed"
End If
line1:
Next
End Sub
--
jetted
------------------------------------------------------------------------
jetted's Profile:
http://www.excelforum.com/member.php...o&userid=17532
View this thread:
http://www.excelforum.com/showthread...hreadid=564693