Work around for Conditional Formatting in Excel 2003

[dmcnab]

Hi all....I am (still) using Excel 2003, with its limited Cond Formatting capacities....I am trying to do some CF using a worksheet change event. This is the code that I am using...I want the cell to colour yellow if I enter ***word........or gray if I enter *word...........whenever I make an entry, I get an error message that says COMPILE ERROR...CASE ELSE OUTSIDE SELECT CASE........can someone tell me what is wrong with the code that I am trying to use? Thank you.....:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
Dim rng As Range
Set rng = Intersect(Target, Range("f12:dk275"))
If Not Intersect(Target, Range("f12:dk275")) Is Nothing Then
For Each oCell In Intersect(Target, Range("f12:dk275")).Cells
Select Case UCase(oCell)
Case "CLOSED"
oCell.Interior.ColorIndex = 12
oCell.Font.ColorIndex = 1
Case Else
If Left(oCell, 1) = "*" Then
oCell.Interior.ColorIndex = 15
oCell.Font.ColorIndex = 1
Case Else
If Left(oCell, 1) = "***" Then
oCell.Interior.ColorIndex = 6
oCell.Font.ColorIndex = 1
Else
oCell.Interior.ColorIndex = xlColorIndexAutomatic
oCell.Font.ColorIndex = 1
End Select
End If
End Sub

[HansV]

1) You forgot Next oCell.
2) You shouldn't mix Case and If, and you shouldn't use more than one Case Else. I'd use If ... End If here, not Select Case.
3) You should test whether the value starts with *** before testing whether it starts with *.
4) To test for ***, you should use Left(oCell, 3) instead of Left(oCell, 1).

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    Dim rng As Range
    Set rng = Intersect(Target, Range("f12:dk275"))
    If Not Intersect(Target, Range("f12:dk275")) Is Nothing Then
        For Each oCell In Intersect(Target, Range("f12:dk275")).Cells
            If UCase(oCell) = "CLOSED" Then
                oCell.Interior.ColorIndex = 12
                oCell.Font.ColorIndex = 1
            ElseIf Left(oCell, 3) = "***" Then
                oCell.Interior.ColorIndex = 6
                oCell.Font.ColorIndex = 1
            ElseIf Left(oCell, 1) = "*" Then
                oCell.Interior.ColorIndex = 15
                oCell.Font.ColorIndex = 1
            Else
                oCell.Interior.ColorIndex = xlColorIndexAutomatic
                oCell.Font.ColorIndex = 1
            End If
        Next oCell
    End If
End Sub

[dmcnab]

Thank you very much for the tips, Hans.....:)...always learning something when I come here....

[sdckapr]

Cross-posted at http://windowssecrets.com/forums/showth ... Excel-2003

Steve