Help with Conditional Formatting

[Bill4tektips]

I am currently working with Excel 2003 and I have a spreadsheet that I have added some code to to change the cell and font colours. If I change the colours to something different from original, I then have to go over all the cells to get them to change to the new colour. Is there any way of adding to the code that will refresh the column. The code I have in is:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim WatchRange As Range
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then
Target.Interior.Color = RGB(255, 255, 255) 'no color
Target.Font.Color = RGB(0, 0, 0)
Exit Sub
Else
CellVal = Target
Set WatchRange = Range("C2:C200")

If Not Intersect(Target, WatchRange) Is Nothing Then
If CellVal = "UK-S" Then
Target.Interior.Color = RGB(153, 204, 0) 'green
Target.Font.Color = RGB(0, 0, 0)
ElseIf CellVal = "UK-L" Then
Target.Interior.Color = RGB(0, 204, 255) 'blue
Target.Font.Color = RGB(0, 0, 0)
ElseIf CellVal = "UK-B" Then
Target.Interior.Color = RGB(255, 102, 0) 'red
Target.Font.Color = RGB(0, 0, 0)
ElseIf CellVal = "France" Then
Target.Interior.Color = RGB(255, 153, 204) 'pink
Target.Font.Color = RGB(0, 0, 0)
ElseIf CellVal = "Italy" Then
Target.Interior.Color = RGB(255, 209, 159) 'orange
Target.Font.Color = RGB(0, 0, 0) 'black
ElseIf CellVal = "E1" Then
Target.Interior.Color = RGB(0, 0, 255) 'blue
Target.Font.Color = RGB(255, 255, 255) 'white
ElseIf CellVal = "C" Then
Target.Interior.Color = RGB(255, 255, 255) 'white
Target.Font.Color = RGB(255, 0, 0) 'red
End If
End If
End If
End Sub

 

[SkipVought]

hi,

The Worksheet_Change event fires when you change one or more VALUES on a sheet, not when you change colors!

I always logically link colors to VALUES on a sheet, so that when the VALUE changes the color will change, either using Conditional Formatting, which in 2003 is limited to only THREE conditions, while in 2007+ as unlimited conditions, or by using VBA.

So your code will fire when a VALUE change occurs in the WatchRange.

Other than that, I am not sure what you are asking, "refresh the column???"

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]