Not event code but just a straight macro to run after your pasting and
rearranging.


Sub namecolor()
Dim Num As Long
Dim rng As Range
Dim vRngInput As Range
Set vRngInput = ActiveSheet.Range("F1:J1000")

On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case UCase(rng.Value)
Case Is = "A": Num = 10 'green
Case Is = "B": Num = 1 'black
Case Is = "C": Num = 5 'blue
Case Is = "D": Num = 7 'magenta
Case Is = "E": Num = 46 'orange
Case Is = "F": Num = 3 'red
Case Else
Num = 0
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

On Thu, 10 May 2007 09:31:01 -0700, Also wrote:

OK,
Seems to make sense, however, I need to do it as part of a Macro that I can
call i.e.:

Sub Name()

I don't know how to convert the scripts starting:

Private Sub Worksheet_Change(ByVal Target As Range)

Currently the Macro pulls some filtered data from one sheet and pastes it
into a new sheet, does some formatting/rearranging etc. So this is actually
the last step in a process.

Thanks all for the help so far.