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

davo1224

New Member
Joined
Dec 12, 2010
Messages
11
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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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:

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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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"
 
Upvote 0
OK, I've changed the code slightly. Can you see if this does the job?

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

                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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top