I have made a performance evaluation system in an Excel Sheet. I am facing one problem in it. In this system, ratings are entered on a letter scale of A, B or C for three headings/columns. Each employee gives rating to each other employee. User can enter A or B for any employee. Blank entries are treated as C. Rating A cannot exceed 30% and rating B cannot exceed 40%. Rating percentages are calculated in cells E27, E28 and E29 for heading 1 as follows:
E27 (For Rating A):
ROUND((COUNTIF($E$4:$E$23,"A")/(COUNTA($D$4:$D$23)))*100,0)
E28 (For Rating B):
ROUND((COUNTIF($E$4:$E$23,"B")/(COUNTA($D$4:$D$23)))*100,0)
E29 (For Rating C):
IF(OR(E27>0,E28>0),100-E28-E27,0)
To control A or B ratings entry above limit, the following code is written:
The problem is that this code works well when the user enters A or B rating in individual cells, but if he selects multiple cells then this code does not work. For example, I entered rating A in cell E4, copied it and pasted it from cell E5 to E23. Now cell E27 is showing 100 whereas in my scenario, cell E27 should never exceed 30.
E27 (For Rating A):
ROUND((COUNTIF($E$4:$E$23,"A")/(COUNTA($D$4:$D$23)))*100,0)
E28 (For Rating B):
ROUND((COUNTIF($E$4:$E$23,"B")/(COUNTA($D$4:$D$23)))*100,0)
E29 (For Rating C):
IF(OR(E27>0,E28>0),100-E28-E27,0)
To control A or B ratings entry above limit, the following code is written:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
If Target.Column <> 5 And Target.Column <> 6 And Target.Column <> 7 Then
Exit Sub
Else
Select Case Target.Column
Case 5
If Target.Value = "A" And Range("$E$27") > 30 Then
MsgBox "Rating A cannot exceed 30%"
Target.ClearContents
ElseIf Target.Value = "B" And Range("$E$28") > 40 Then
MsgBox "Rating B cannot exceed 40%"
Target.ClearContents
Else
End If
Case 6
If Target.Value = "A" And Range("$F$27") > 30 Then
MsgBox "Rating A cannot exceed 30%"
Target.ClearContents
ElseIf Target.Value = "B" And Range("$F$28") > 40 Then
MsgBox "Rating B cannot exceed 40%"
Target.ClearContents
Else
End If
Case 7
If Target.Value = "A" And Range("$G$27") > 30 Then
MsgBox "Rating A cannot exceed 30%"
Target.ClearContents
ElseIf Target.Value = "B" And Range("$G$28") > 40 Then
MsgBox "Rating B cannot exceed 40%"
Target.ClearContents
Else
End If
End Select
End If
ErrorHandler:
Exit Sub
End Sub
The problem is that this code works well when the user enters A or B rating in individual cells, but if he selects multiple cells then this code does not work. For example, I entered rating A in cell E4, copied it and pasted it from cell E5 to E23. Now cell E27 is showing 100 whereas in my scenario, cell E27 should never exceed 30.