Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Sep 2001
    Location
    Birmingham, Alabama, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Activ Cell Color (Excel 2000)

    About 3 years ago Brook posted the following Macro to change the cell color of the active cell.

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Static OldCell As Range
    If Not OldCell Is Nothing Then OldCell.Interior.ColorIndex = 0
    Target.Interior.ColorIndex = 6
    Set OldCell = Target
    End sub

    This does exactly what I need but I have one problem. When the macro is running I cannot copy or cut and past a cell. I think I understand what is happening but I have no idea how to correct the problem. Can anyone help?

    Thanks

    M

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Activ Cell Color (Excel 2000)

    The following is based on a newsgroup posting:

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Static OldCell As Range
    Static rngCutCopy As Range
    Dim intCutCopyMode As Integer
    intCutCopyMode = Application.CutCopyMode
    If Not OldCell Is Nothing Then OldCell.Interior.ColorIndex = 0
    Target.Interior.ColorIndex = 6
    Select Case intCutCopyMode
    Case xlCopy
    If rngCutCopy Is Nothing Then
    Set rngCutCopy = OldCell
    End If
    rngCutCopy.Copy
    Case xlCut
    If rngCutCopy Is Nothing Then
    Set rngCutCopy = OldCell
    End If
    rngCutCopy.Cut
    Case Else
    Set rngCutCopy = Nothing
    End Select
    Set OldCell = Target
    End Sub

  3. #3
    Lounger
    Join Date
    Sep 2001
    Location
    Birmingham, Alabama, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Activ Cell Color (Excel 2000)

    Hans,

    Thanks for the quick reply. I am too new at VBA to figure out the one problem this routine seems to have. If I copy and past a cell everything works great. If I Cut a cell the cut works finne and the value is pasten into the new cell. When the cell pointer is moved to another location the macro returns a run time error 424. The line that is returning the error is:

    If Not OldCell Is Nothing Then OldCell.Interior.ColorIndex = 0

    I can't figure out what the diffrence is between cut and copy.

    Thanks again for the help.

    M

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Activ Cell Color (Excel 2000)

    Frankly, I can't explain this. After cutting and pasting a cell, OldCell is not a valid object any more, although the test OldCell Is Nothing returns False. An easy but unsatisfactory way out is to insert a line

    On Error Resume Next

    at the start of the procedure.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Activ Cell Color (Excel 2000)

    Just a "thought":
    I think you are in one of the "odd places". You have referenced an object and then essentially "destroyed" it. It was not set to nothing as if it did not exist, it is "nuked".

    Steve

  6. #6
    Lounger
    Join Date
    Sep 2001
    Location
    Birmingham, Alabama, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Activ Cell Color (Excel 2000)

    Hans,

    Thanks again. For what I am doing I think your fix is acceptable.

    M

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Activ Cell Color (Excel 2000)

    I was looking at the code this morning and found some "bugs" in it.
    If you copy a cell and paste it. the rngCutCopy remains (good) so you can continue to paste the selection).

    If however after copying, you select a cell and CUT it when the code gets to the Case:
    <pre>Case xlCut
    If rngCutCopy Is Nothing Then
    Set rngCutCopy = OldCell
    End If</pre>


    The rngCutCopy is NOT nothing (it is the cell you previously copied) and you will CUT not what you just "cut" but what you had previously selected. It is even the same situation when you copy a new selection:
    Also once you COPY, it keeps that as the item to copy or move it keeps the original COPIED item until you do a move or hit escape to get out of cut/copy mode AND then SELECT another cell (if you only hit escape, and then copy, it will pick up the original selection again)

    I don't know if there is another way, but the easiest solution is just to remove both sequences:
    <pre> If rngCutCopy Is Nothing Then
    Set rngCutCopy = OldCell
    End If</pre>


    and just use:
    <pre> Set rngCutCopy = OldCell</pre>


    This will still allow "multiple copies" it will just keep using the copied cells instead of the original.

    Steve

Posting Permissions

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