IF statement with Conditional Formatting.

**Annisha** · 07-05-2011, 06:02 AM

Hi,

I have a performance spreadsheet which is setup to identify how different services are performing. I use the code below to identify the performace. This code does not update when I use IF statement or any simple formula. For example IF(A1=10,"h","hh"), this will not be update the colour once I have changed cell A1 to 10, although the formula will work and change to "h".

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo ws_exit:
    Application.EnableEvents = False
    If Not Intersect(Target, Range("A1:V1000")) Is Nothing Then
       With Target
            Select Case Target.Value
            
                Case (""): .Interior.ColorIndex = 0 'default
                            With Selection.Font
                                 .Name = "Marlett"
                                 .Size = 10
                            End With
                Case ("h"): .Interior.ColorIndex = 3 'red
                Case ("hh"): .Interior.ColorIndex = 45 'orange
                Case ("hhh"): .Interior.ColorIndex = 6 'yellow
                Case ("hhhh"): .Interior.ColorIndex = 43 'green
                Case ("hhhhh"): .Interior.ColorIndex = 10 'Dark Green
                
            End Select
      End With
    End If

ws_exit:
    Application.EnableEvents = True
End Sub

I wonder if anyone can help me.

Thanks

**TMS** · 07-05-2011, 06:15 AM

Welcome to the forum Annisha. Please take a moment to read the forum rules and add CODE tags to your code example.

It's often useful to post a sample workbook with some typical data.

As a pointer, you're changing the value in column A but your case statement is looking at the value in the cell affected by the change in column A. You should modify your Select Case statement to monitor the outcome cell.

Regards

**Annisha** · 07-05-2011, 06:36 AM

Hi TMShucks,

I would have posted the worksheet as the data need to be protected so I cannot.

I am beginners in Excel VB, I don't seem to understand what you mean by modifying Select Case statement.