Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Change cell background color if cell is copied.

    Is there a way to change the background of a cell if a user initiates a CTRL+C or right clicks and selects Copy?

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    KW,

    Here is the code to do what you want. If the user right clicks on a cell or range of cells then selects copy, the cell(s) turn red. The same occurs if CTRL-C is used to copy.

    HT,
    Maud

    Copy to ThisWorkbook Module:
    Code:
    Private Sub Workbook_Deactivate()
    '----------------------------------
    'REMOVES CUSTOM CONTEXT MACRO AND RESETS CONTEXT MENU BACK TO DEFAULT
        On Error Resume Next
            With Application
                .CommandBars("Cell").Controls("&Copy").Delete
                .CommandBars("Cell").Reset
            End With
            Application.MacroOptions Macro:="CheckCopy", Description:="", ShortcutKey:=""
        On Error GoTo 0
    End Sub
    
    Private Sub Workbook_Open()
    '----------------------------------
    'SETS CTRL-C TO RUN THE CHECKCOPY MACRO
        Application.MacroOptions Macro:="CheckCopy", Description:="", ShortcutKey:="c"
    End Sub
    
    Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    '----------------------------------
    'CREATES CUSTOM CONTEXT MACRO
        Dim MyMacro As CommandBarButton
        On Error Resume Next
            With Application
                .CommandBars("Cell").Reset
                .CommandBars("Cell").Controls("&Copy").Delete
                Set MyMacro = .CommandBars("Cell").Controls.Add(Temporary:=True)
            End With
            With MyMacro
               .Caption = "&Copy"
               .Move Before:=2
               .Style = msoButtonCaption
               .OnAction = "CheckCopy"
            End With
        On Error GoTo 0
    End Sub
    Copy to a Standard module:
    Code:
    Public Sub CheckCopy()
    '----------------------------------
    'COPY FUNCTION AND CELL COLOR CHANGE
        Selection.Copy
        Selection.Interior.Color = vbRed
    End Sub
    Attached Files Attached Files

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,830
    Thanks
    136
    Thanked 483 Times in 460 Posts
    KW

    It's not entirely clear what you want.
    Maud shows one way (but in the posted sample file it would still be possible to copy cells to another location without setting the red cell backgrounds).

    What if the User presses Ctrl-C and then cancels with an Esc?
    Do you want both the copied source-cells and the destination-cells to be red, or just the destination-cells?

    zeddy

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I only want the source cells to change color. Maud's version changes both the source and destination cells. The user copies a block of cells, pastes them into another workbook, then returns to the original worksheet. Once back on the original, she wants to have kept track automatically which ones she copied.

    You brought up an interesting issue of doing a copy then cancel it. I can see that happening if she selects the wrong cells and cancels before pasting into the other workbook.
    Last edited by kweaver; 2016-06-11 at 07:20.

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    KW,

    Revised the code to remove the Red cell background of the copied cells if the user cancels the paste (ESC). Also corrected is the pasted cell(s) issue that no longer turns red using the context menu or ctrl-v.

    HTH,
    Maud
    Attached Files Attached Files
    Last edited by Maudibe; 2016-06-11 at 09:09.

  6. The Following 2 Users Say Thank You to Maudibe For This Useful Post:

    kweaver (2016-06-11),RetiredGeek (2016-06-11)

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    Nice piece of coding!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Pasting in another workbook? Hmmm....

    Thanks RG

  9. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,830
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi Maud

    When I used your file, I found that copying any cells to the same sheet made the source cells red.
    When I copied cells to another workbook, the destination cells were also red.
    If I selected a group of cells, pressed Ctrl-C for Copy, then [Esc] to cancel, it still left some cells red.

    I used a different method in the attached workbook.
    In my version, cell formats of copied-cells are preserved when copied to a destination workbook.
    Copying cells on the same source sheet doesn't affect their formats.
    It's only when copying to a another workbook that they are 'turned red'
    ..and if you try and cheat, i.e. select a group of cells to copy, then switch to another workbook, but then decide not to do anything (i.e. didn't do the copy), then when you come back to the source workbook, the source cells are NOT turned red.

    It's not perfect (I still know a way of copying cells from the source to a destination workbook which bypasses the wanted change to the cell background)

    zeddy
    Attached Files Attached Files

  10. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Here is a revision that allows pasting to the existing sheet, a different sheet, or a different workbook without carrying the formatting to the destination cells. The only caveat is that when you copy the cell(s), you do not get the copy mode dotted lines around the copied cells. Pasting the data remains unchanged.

    Maud
    Attached Files Attached Files

  11. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,830
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi Maud

    From KW's post#4:
    The user copies a block of cells, pastes them into another workbook, then returns to the original worksheet.
    I cannot copy a block of cells from the source file to a new workbook.
    If I try to copy multiple cells, then cancel with [Esc], it marks some of the cells as being 'copied' when they haven't been. It seems to only assume one cell is being copied.

    If my source cells have any formatting (e.g. font, color etc etc ) these are not copied to the destination workbook.
    ..and if I cancel a 'copy', I lose the original cell formats.
    I thought my posted version dealt with this OK.

    zeddy

Tags for this Thread

Posting Permissions

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