Results 1 to 9 of 9

macro not working on sheet change

Threaded View

  1. #1
    Registered User
    Join Date
    09-07-2011
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    9

    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
    Attached Images Attached Images

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1