Refresh colour in conditional formating excel

Closed
confused - Aug 13, 2009 at 01:32 AM
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 - Aug 15, 2009 at 11:31 PM
Hello,

I have used the below vba code to format a drop down cell based on whether its pass or fail or block in excel(2000)
'START OF CODE
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.Count > 1 Then Exit Sub
CellVal = Target
Set WatchRange = Range("F18:F197")

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case "Pass"
Target.Interior.ColorIndex = 42
Case "Fail"
Target.Interior.ColorIndex = 7
Case "Block"
Target.Interior.ColorIndex = 5
Case "TBD"
Target.Interior.ColorIndex = 29
End Select
End If
End Sub
'END OF CODE
The problem is if i change the colour value of "Block" in the code from 5 to 10 the colour does not get automatically updated in the sheet. I need to select "Block" again from the drop down menu for it to work.

Is there a way i can automatically refresh it
Related:

1 response

Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Aug 15, 2009 at 11:31 PM
Try this

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.Count > 1 Then Exit Sub
CellVal = Target
Set WatchRange = Range("A1:F7")

'If Not Intersect(Target, WatchRange) Is Nothing Then
For Each cll In WatchRange
Select Case cll.Value
Case "Pass"
cll.Interior.ColorIndex = 6
Case "Fail"
cll.Interior.ColorIndex = 7
Case "Block"
cll.Interior.ColorIndex = 5
Case "TBD"
cll.Interior.ColorIndex = 9
Case Else
cll.Interior.ColorIndex = 0
End Select
Next
'End If
End Sub
0