Copy text from cell to cell with one cell changing text

[Guest]

I want to copy text from cell Sheet1 B1 to cell Sheet2 C3. Then when text is
changed in cell Sheet1 B1 the new text is copied to next cell in Sheet2
columnC (cell C4) and so on.

 

[Guest]

Put the following in Sheet1 worksheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("B1"), Target) Is Nothing Then
Exit Sub
End If
n = Sheets("Sheet2").Cells(Rows.Count, "C").End(xlUp).Row + 1
If n < 3 Then
n = 3
End If
Set r2 = Sheets("Sheet2").Cells(n, "C")
Target.Copy r2
End Sub

 

[Guest]

Works but I need adjustments. 1 How do I change format when text is moved to
C3. I tried conditional formating but it does not allow to change font. 2
The formula I used for C3 is CELL "contents" Is this correct? 3 I have a
drop down button in B1. When text was copied to C3 the drop down followed.
How do I eliminate that?

Thanks

 

[Guest]

Removing the drop-down is easy.


What kind of formattting do you wish to apply to C3 (and on down) after the
paste??

 

[Guest]

Formatting is different in column C - pattern, font, no drop down arrow and
so on. I tried format and conditional formatting.

Thanks

 

[Guest]

Formatting is different in column C - pattern, font, no drop down arrow and
so on. I tried format and conditional formatting.