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.
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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:

davo1224

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

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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

New Member
Joined
Dec 12, 2010
Messages
11

ADVERTISEMENT

It changes with C4
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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

New Member
Joined
Dec 12, 2010
Messages
11

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,496
Messages
5,596,497
Members
414,071
Latest member
hijackhippo

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
Top