Change color of cell to Yellow once I copy it

[Saher Naji]

Hello,

I'm trying to use this code to change the background color of a cell to yellow when I press Ctrl+C and has it stay yellow. This should be applied to every cell I copy.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Application.CutCopyMode = xlCopy Then
        Target.Interior.Color = RGB(255, 255, 0) ' Yellow color
    Else
        Target.Interior.Pattern = xlNone ' Reset the cell's fill color
    End If
End Sub

Thank you in advance.

 

[KOKOSEK]

I am quite sure that I understand correctly, but maybe you can try by Worksheet_Change not Worksheet_SelectionChange, like below:

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.CutCopyMode = 0 Then Exit Sub
If Application.CutCopyMode = xlCopy Then
    Target.Interior.Color = RGB(255, 255, 0)
End If
End Sub

 

[Saher Naji]

I am quite sure that I understand correctly, but maybe you can try by Worksheet_Change not Worksheet_SelectionChange, like below:

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.CutCopyMode = 0 Then Exit Sub
If Application.CutCopyMode = xlCopy Then
    Target.Interior.Color = RGB(255, 255, 0)
End If
End Sub

Hi @KOKOSEK
Thank you for your response. The code you provided worked and highlighted the cells with a yellow color after I paste the values.
But, I would like the cell to change to yellow immediately after selecting it and pressing Ctrl+C.

 

[KOKOSEK]

So change:

Worksheet_Change

into

Worksheet_SelectionChange

 

[Saher Naji]

We are almost there. Now, when you press Ctrl+C, nothing happens, and I have to click on another cell with the mouse to make them turn yellow.

 

[Joe4]

The issue is this:
- "Worksheet_Change" is fired when a value is manually changed in a cell
- "Worksheet_SelectionChange" is fired when a cell is selected

There is no built-in event to fire when CTRL+C, or copy, is selected.

I think you would have to get a little creative with your solutions. Check out some of suggestions here: VBA event trigger on copy?

 

[Saher Naji]

@Joe4

Thank you very much, I checked out some suggestions, and it worked very well, I used this code, and I assigned a shortcut key to my macro: Ctrl+C

Sub CopyAndMarkAsCopied()
    Dim r As Range
    Set r = Selection
    With r
        .Copy
        .Font.Color = RGB(0, 0, 0) 'black font
        .Interior.Color = RGB(255, 255, 0) 'yellow background
        'whatever else
    End With
End Sub

 

[Joe4]

Excellent!
Glad to hear you got something that works for you!