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

change a range colour after copy to another worksheet
 

R.Font.Colorindex=xlNone

HTH
--
AP

"Kezza" a écrit dans le message de news:
...
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

change a range colour after copy to another worksheet
 

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

change a range colour after copy to another worksheet
 

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" a écrit dans le message de news:
...
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