Change color of cell when contents come from copy/paste from other WB

[gautammalkani]

Hi

how do I change the color of cell (for example green) when the contents are either inputted through a copy and paste from another worksheet.

Thanks for all your help!!!

Gautam

PS I am using this function, which does not seem to work :

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 4
End Sub

 

[SkipVought]

Hi,

What, exactly, does not seem to work?

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[gautammalkani]

Hi SkipVought

The color of the cell does not change, when I copy and paste from another worksheet. I want to change the color of the cells when the user copies and pastes from another worksheet or if the external link has a different color for their cell. Thanks for your help.

Gautam

 

[SkipVought]

A Paste operation has no way of knowing the source. It pastes from the Clipboard.

It might be possible to determine an external reference for FORMULAS, however. Look at the form of the externla reference as compared to a reference from another sheet within the same workbook....

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If Left(.Formula, 3) = "='[" Then
            .Interior.Colorindex = 4
        End If
    End With
End Sub

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[SkipVought]

If you have a range of cells, the procedure needs to be, rather...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim t As Range
    For Each t In Target
        With t
            If Left(.Formula, 3) = "='[" Then
                .Interior.ColorIndex = 4
            End If
        End With
    Next
End Sub

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]