change a range colour after copy to another worksheet

Hi all, I would like to change the text colour in a range after copy
from red to black. Here is my current macro:

Dim R As Range
On Error Resume Next
Set R = Application.InputBox("Select A Certificate Number:", _
"Transfer Clearance Register Information", Type:=8)
If R Is Nothing Then Exit Sub
On Error GoTo 0
Set R = R.Resize(1, 5)
R.Copy Sheets(2).Cells(65000, 1).End(xlUp).Offset(1, 0)
Sheets("Non-Conformances").Select

Hope you can help
Thanks

R.Font.Colorindex=xlNone

HTH
--
AP

"Kezza" <[email protected]> a écrit dans le message de news:
[email protected]...
> Hi all, I would like to change the text colour in a range after copy
> from red to black. Here is my current macro:
>
> Dim R As Range
> On Error Resume Next
> Set R = Application.InputBox("Select A Certificate Number:", _
> "Transfer Clearance Register Information", Type:=8)
> If R Is Nothing Then Exit Sub
> On Error GoTo 0
> Set R = R.Resize(1, 5)
> R.Copy Sheets(2).Cells(65000, 1).End(xlUp).Offset(1, 0)
> Sheets("Non-Conformances").Select
>
> Hope you can help
> Thanks
>

Thanks heaps Ardus for your help. Unfortunately I did not explain what
I require very well. Sorry! I would like the text on the second
worksheet to be black after paste while the original text on worksheet
1 remains red. (I hate being a begginer at this stuff but I guess this
is how we learn).
Thanks again

Sub test()
Dim R As Range
Dim rDest As Range
On Error Resume Next
Set R = Application.InputBox("Select A Certificate Number:", _
"Transfer Clearance Register Information", Type:=8)
If R Is Nothing Then Exit Sub
On Error GoTo 0
Set R = R.Resize(1, 5)
Set rDest = Sheets(2).Cells(Rows.Count).End(xlUp).Offset(1, 0)
R.Copy rDest
rDest.Font.ColorIndex = xlNone
Sheets("Non-Conformances").Select
End Sub

HTH
--
AP

"Kezza" <[email protected]> a écrit dans le message de news:
[email protected]...
> Thanks heaps Ardus for your help. Unfortunately I did not explain what
> I require very well. Sorry! I would like the text on the second
> worksheet to be black after paste while the original text on worksheet
> 1 remains red. (I hate being a begginer at this stuff but I guess this
> is how we learn).
> Thanks again
>