Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = True
Dim chkRng As Range, chkRng2 As Range, i As Long
Set chkRng = Range("BF7:BF150,W7:W150")
Set chkRng2 = Range("BC7:BC150")
If Not Intersect(Target, chkRng2) Is Nothing Then
For i = 7 To 150 Step 1
If Cells(i, "BC").Value = Application.WorksheetFunction.Max(chkRng2) Then
Range("R" & i & ":BD" & i).Interior.colorindex = 6
Else
Range("R" & i & ":BD" & i).Interior.colorindex = 0
End If
Next i
End If
If Not Intersect(Target, chkRng) Is Nothing Then
Select Case Cells(Target.Row, "BF").Value
Case 1
Range(Cells(Target.Row, "R"), Cells(Target.Row, "BD")).Font.colorindex = 4
Case 2
Range(Cells(Target.Row, "R"), Cells(Target.Row, "BD")).Font.colorindex = 8
Case Is >= 3
Range(Cells(Target.Row, "R"), Cells(Target.Row, "BD")).Font.colorindex = 9
Case Else
With Range(Cells(Target.Row, "R"), Cells(Target.Row, "BD"))
.Font.colorindex = xlAutomatic
End With
End Select
If Cells(Target.Row, "W").Value = 1 Then _
Range(Cells(Target.Row, "R"), Cells(Target.Row, "BD")).Interior.colorindex = 6
End If
End Sub