Refresh colour in conditional formating excel [Closed]

Report
-
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
-
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

1 reply

Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!