Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi there
    I have this macro and it works great, however, my manager (in his ultimate wisdom) has decided to change numbers to letters, and I've tried making changes myself but it doesn't seem to work. Macro's aren't my strongest point.

    Instead of 1,2,3 the letters are MA, MB, MC etc.. This time the cells are linked.

    So what and how do I change my macro.

    Thanks for all the help


    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("A4:AG4")) Is Nothing Then
    For Each oCell In Intersect(Target, Range("A4:AG4")).Cells
    Select Case (oCell)
    Case 1
    oCell.Interior.ColorIndex = 4
    Case 2
    oCell.Interior.ColorIndex = 3
    Case 3
    oCell.Interior.ColorIndex = 8
    Case 4
    oCell.Interior.ColorIndex = 6
    Case 5
    oCell.Interior.ColorIndex = 7
    Case 6
    oCell.Interior.ColorIndex = 15
    Case Else
    oCell.Interior.ColorIndex = xlColorIndexAutomatic
    End Select
    Next oCell
    End If
    End Sub

  2. #2
    Lounger
    Join Date
    Mar 2009
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("A4:AG4")) Is Nothing Then
    For Each oCell In Intersect(Target, Range("A4:AG4")).Cells
    Select Case (oCell)
    Case "MA"
    oCell.Interior.ColorIndex = 4
    Case "MB"
    oCell.Interior.ColorIndex = 3
    Case "MC"
    oCell.Interior.ColorIndex = 8
    Case "MD"
    oCell.Interior.ColorIndex = 6
    Case "ME"
    oCell.Interior.ColorIndex = 7
    Case "MF"
    oCell.Interior.ColorIndex = 15
    Case Else
    oCell.Interior.ColorIndex = xlColorIndexAutomatic
    End Select
    Next oCell
    End If
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks for your help but it didn't work. I've tried all options.. I'm sure I'm missing the most obvious...

    Thanks anyway

  4. #4
    Lounger
    Join Date
    Mar 2009
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If you enter MA in a cell say B4, what happens? Have you tried stepping through the code?

  5. #5
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Nothing happens, no background colour, it stays white

  6. #6
    Lounger
    Join Date
    Mar 2009
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Try this
    Attached Files Attached Files

  7. #7
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts
    It worked...
    Have no idea when it didn't work before when I copied and pasted your formula, but now it works, so thanks heaps...

    PS where you from being online at this time of day ?? Usually its just me

  8. #8
    Lounger
    Join Date
    Mar 2009
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm from Scotland. Late night tonight, but time to go now.

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    [quote name='mdmackillop' post='767432' date='26-Mar-2009 01:52']I'm from Scotland. Late night tonight, but time to go now.[/quote]

    On the basis that there can't be too many people using that username, it's great to see you here! Visiting from VBAX?
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Lounger
    Join Date
    Mar 2009
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes, I happened across the site, and it was quiet "over there". I see you're a founder member!

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Not exactly - the old, old, old, old Lounge predates me by some way! (I think that's the right number of 'old's!)
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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