Paste a cell's contents if another cell's value changes

[davo1224]

I have a worksheet where the date is always located in C4. What I need to happen is that if the letter T is entered in any of the cells in column G, then I'd like the contents of C4 to be pasted in column H. The catch is that I want what's pasted to be static like Paste Special > Values only. Could someone please help me do this? Thank you in advance.

 

[dk]

You could use a formula based solution such as

=IF(G7="T",C4,"")

You could also use VBA such as this. In order to use this code you just need to right click the worksheet tab name and paste this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngChanged As Range
    Dim rngCell As Range

    On Error Resume Next

    Set rngChanged = Intersect(Me.Range("G:G"), Target)

    If Not rngChanged Is Nothing Then

        Application.EnableEvents = False
        
        For Each rngCell In rngChanged.Cells
    
            If rngCell.Value = "T" Then
                Me.Range("H" & rngCell.Row).Value = Me.Range("C4").Value
                Me.Range("H" & rngCell.Row).NumberFormat = Me.Range("C4").NumberFormat
            End If

        Next rngCell

    Application.EnableEvents = True


    End If
    
    

End Sub

 

[davo1224]

And this will keep the value that was pasted at the time "T" was input into the cell correctly? Cell C4 changes daily so I only want the contents of C4 when T gets typed in to be pasted and not change.

 

[dk]

The formula approach would not work in that case but the VBA will insert the value and might do what you need. Try it and see if it works.

 

[davo1224]

It changes with C4

 

[dk]

What changes with C4? As far as I can tell the code does exactly what you asked for. If not then you need to do a better job of explaining what you need.

 

[davo1224]

Sorry but not really sure how I can explain it better. C4 is the date, which changes daily.

If G7 for instance has a "T", I want H7 to have the value inside $C$4. When $C$4 gets a new value, I want the value that was pasted in H7 to stay the same and not change with $C$4. Similarly, if the "T" in G7 is removed or changed to anything else, I want H7 to be blank.

I know it involves a VBA but my issue is that I don't know how to properly use

"Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False"

 

[dk]

OK, I've changed the code slightly. Can you see if this does the job?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngChanged As Range
    Dim rngCell As Range

    On Error Resume Next

    Set rngChanged = Intersect(Me.Range("G:G"), Target)

    If Not rngChanged Is Nothing Then

        Application.EnableEvents = False

        For Each rngCell In rngChanged.Cells

            If rngCell.Value = "T" Then

                If IsDate(Me.Range("H" & rngCell.Row).Value) = False Then

                    Me.Range("H" & rngCell.Row).Value = Me.Range("C4").Value
                    Me.Range("H" & rngCell.Row).NumberFormat = Me.Range("C4").NumberFormat

                End If

            Else
                Me.Range("H" & rngCell.Row).Value = Null
            End If


        Next rngCell

        Application.EnableEvents = True


    End If



End Sub

 

[davo1224]

Bingo! Thanks very much