macro not working on sheet change

Afternoon All

I have an excel 2003 workbook with a macro in, the macro changes the colour of a cell based on its value. the macro works up to a point.

on the 1st sheet we have cells all linked to the contents of cells in other sheets
"screen1.jpg"

if I go to another sheet, and change the value from g to X the cell turns black, which is what the macro is meant to do.
"screen2.jpg"

if we then go back to the 1st sheet excel notices that the value has changed from G to X but has made the background white?
if I manually type in X it goes black,

Does anyone know anything I could be doing wrong here?

the macro is:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Target
        Select Case oCell.Value
             Case Is = "R", "r"
                 oCell.Interior.Color = RGB(255, 0, 0)
             Case Is = "G", "g"
                 oCell.Interior.Color = RGB(0, 255, 0)
             Case Is = "A", "a"
                 oCell.Interior.Color = RGB(255, 102, 0)
             Case Is = "B", "b"
                 oCell.Interior.Color = RGB(0, 0, 255)
             Case Is = "x", "X"
                 oCell.Interior.Color = RGB(0, 0, 0)
             Case Is = "BRX", "brx"
                 oCell.Interior.Color = RGB(100, 100, 100)
             Case Else
                 oCell.Interior.ColorIndex = xlNone
         End Select
    Next oCell
End Sub

thanks in advance for any replies

Hi David777,

the tricky thing with events is that often they are switched off at the beginning of macros, and switched on again at the end. If the macro fails (or is stopped by the user) the event handler remains switched of.

Maybe you should run this simple macro before trying yours again:

Sub switchOnEvents()
    application.enableevents = true
end sub

Regards
Theo

afternoon all

thankyou for the quick replies

unfortunately none of the suggestions worked

FetterTiger - this didnt seem to make any difference
Mordred - this stopped the macro working completely

Any other ideas would be greatly appreciated

regards

Evening all

I've made a sample worksheet to display the error

the cells in sheet 1 are linked to the cells in sheet 2,
however if you change a value in sheet 2 it correctly changse the value in sheet1 but doesnt run the macro to change the cell colour on sheet 1

any ideas?

regards

If there anything in excel that forces a macro to run every few seconds? that would be a way of getting what im after

regards

Dave

Hi David777,

the reason for the "strange" behavour is that the "WorksheetChange" event is not triggered by changes done through formulas. What you could use instead is the "WorksheetCalculate" Event. Unfortunately the WorksheetCalculate does not know the "Target", therefore you might need to define the range of cells, on which the macro should act on manualy. You can see how this is done in the sample code below:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim oCell As Range
Dim t As Range
Set t = Sheet1.Range("b3:b8")
For Each oCell In t
        Select Case oCell.Value
             Case Is = "R", "r"
                 oCell.Interior.Color = RGB(255, 0, 0)
             Case Is = "G", "g"
                 oCell.Interior.Color = RGB(0, 255, 0)
             Case Is = "A", "a"
                 oCell.Interior.Color = RGB(255, 102, 0)
             Case Is = "B", "b"
                 oCell.Interior.Color = RGB(0, 0, 255)
             Case Is = "x", "X"
                 oCell.Interior.Color = RGB(0, 0, 0)
             Case Is = "BRX", "brx"
                 oCell.Interior.Color = RGB(100, 100, 100)
             Case Else
                 oCell.Interior.ColorIndex = xlNone
         End Select
    Next oCell
End Sub

Hope that helps
Regards
Theo
__________________
Please consider:

Thanking those who helped you. Click the scales icon in the upper right corner of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.