The Cells that are filled in keep a color if they are blank.
Would like to have them set to White if they are blank.
Try this
It removed all color from the range and them applies color based on values
(BTW, it's not really necessary to quote the entire message when replying, only the pertinent parts if any)
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
Range("B2:I20").Interior.ColorIndex = xlColorIndexNone
For Each rCell In Range("B2:I20").Cells
With rCell
Select Case .Value
Case "Red"
.Interior.ColorIndex = 3
Case "Yellow"
.Interior.ColorIndex = 6
Case "Green"
.Interior.ColorIndex = 43
Case "OUT OF COMPLIANCE"
.Interior.ColorIndex = 3
Case "REVISING"
.Interior.ColorIndex = 6
Case "IN COMPLIANCE"
.Interior.ColorIndex = 43
Case "IN REVIEW"
.Interior.ColorIndex = 45
Case "Grade 1"
.Interior.ColorIndex = 3
Case "Grade 2"
.Interior.ColorIndex = 45
Case "Grade 3"
.Interior.ColorIndex = 6
Case "Pass"
.Interior.ColorIndex = 43
End Select
End With
Next
End Sub
Here's an alternative, little more efficient and cleaner.
1. Checks to see if the cells that were changed are in B2:I20 and exits if not
2. Only checks the cells that were actually changed, not all of them in B2:I20 (might be single cell typically I'm thinking)
3. Tests UPPER CASE so that 'pass' and 'Pass' and 'PASS' are get color
4. Uses some built in .Color (not .ColorIndex) constants
5. Consolidates the conditions in the 'Case' statement
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range, rChanged As Range
Set rChanged = Intersect(Range("B2:I20"), Target)
If rChanged Is Nothing Then Exit Sub
For Each rCell In rChanged.Cells
With rCell
If Len(.Value) = 0 Then
.Interior.ColorIndex = xlColorIndexNone
Else
Select Case UCase(.Value)
Case "RED", "GRADE 1", "OUT OF COMPLIANCE"
.Interior.Color = vbRed
Case "YELLOW", "REVISING", "GRADE 3"
.Interior.Color = vbYellow
Case "GREEN", "PASS", "IN COMPLIANCE"
.Interior.Color = vbGreen
Case "IN REVIEW", "GRADE 2"
.Interior.ColorIndex = 45
End Select
End If
End With
Next
End Sub