ActiveCell Format Same as Target.Address Format

I'm using code where every time a user makes a change anywhere on a specific
sheet, it records that change on another (hidden) sheet. What can I add to
my code (copy of which is below) so that the new value that is entered will
show up on the hidden sheet formatted the same way (i.e., either as text, 1
decimal, 2 decimals, etc.)?

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("$A$17:$U$25")) Is Nothing Then
With Worksheets("Rates-History")
.Activate
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Activate
ActiveCell.Value = Target.Value
End With
End If

End Sub

It would be easier to do a copy.pasteSpecial:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("$A$17:$U$25")) Is Nothing Then
Target.Copy
With Worksheets("Rates-History").(Rows.Count, "A").End(xlUp).Offset(1, 0)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
Application.CutCopyMode = False
End If

End Sub

--
- K Dales


"Paige" wrote:

> I'm using code where every time a user makes a change anywhere on a specific
> sheet, it records that change on another (hidden) sheet. What can I add to
> my code (copy of which is below) so that the new value that is entered will
> show up on the hidden sheet formatted the same way (i.e., either as text, 1
> decimal, 2 decimals, etc.)?
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Not Application.Intersect(Target, Range("$A$17:$U$25")) Is Nothing Then
> With Worksheets("Rates-History")
> .Activate
> .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Activate
> ActiveCell.Value = Target.Value
> End With
> End If
>
> End Sub
>

Works great - thanks!

"K Dales" wrote:

> It would be easier to do a copy.pasteSpecial:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Not Application.Intersect(Target, Range("$A$17:$U$25")) Is Nothing Then
> Target.Copy
> With Worksheets("Rates-History").(Rows.Count, "A").End(xlUp).Offset(1, 0)
> .PasteSpecial xlPasteValues
> .PasteSpecial xlPasteFormats
> End With
> Application.CutCopyMode = False
> End If
>
> End Sub
>
> --
> - K Dales
>
>
> "Paige" wrote:
>
> > I'm using code where every time a user makes a change anywhere on a specific
> > sheet, it records that change on another (hidden) sheet. What can I add to
> > my code (copy of which is below) so that the new value that is entered will
> > show up on the hidden sheet formatted the same way (i.e., either as text, 1
> > decimal, 2 decimals, etc.)?
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Not Application.Intersect(Target, Range("$A$17:$U$25")) Is Nothing Then
> > With Worksheets("Rates-History")
> > .Activate
> > .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Activate
> > ActiveCell.Value = Target.Value
> > End With
> > End If
> >
> > End Sub
> >