Change cell background color if cell is copied.

Topic

New Reply

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

Reply | Quote

Replies

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()
[COLOR=”#008000″]’———————————-
‘REMOVES CUSTOM CONTEXT MACRO AND RESETS CONTEXT MENU BACK TO DEFAULT[/COLOR]
    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()
[COLOR=”#008000″]’———————————-
‘SETS CTRL-C TO RUN THE CHECKCOPY MACRO[/COLOR]
    Application.MacroOptions Macro:=”CheckCopy”, Description:=””, ShortcutKey:=”c”
End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
[COLOR=”#008000″]’———————————-
‘CREATES CUSTOM CONTEXT MACRO[/COLOR]
    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()
[COLOR=”#008000″]’———————————-
‘COPY FUNCTION AND CELL COLOR CHANGE[/COLOR]
    Selection.Copy
    Selection.Interior.Color = vbRed
End Sub

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Maud,

Nice piece of coding! :thewave: :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Pasting in another workbook? Hmmm….

Thanks RG

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote