You need an event macro for that:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Worksheets("Munka1").Cells(Target.Row, Target.Column).Copy
Target.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Cells(Target.Row + 1, Target.Column).Select
Application.CutCopyMode = False
Application.EnableEvents = True
End Sub


Post if you need help to install it!
Regards,
Stefi

€žThe Message€� ezt Ã*rta:

Thanks John, but I want this to happen automatically -

eg in worksheet 1, cell B2 is red

I want to type into worksheet 2,

=((cell b2) worksheet 1) and the cell to turn red

Can you help?

"John" wrote:

Hi
Copy Paste Special Select Format
HTH
John
"The Message" wrote in message
...
I expect there is any easy answer to this but I am basically wanting to copy
the details of one worksheet into another. Using the basic '= Sheet 1! etc.'
works fine for text, numbers and formulas, but does not seem to work for
simply copying a coloured cell from one sheet to another. The cell has no
formula, number or text. It is simply coloured. All efforts so far have not
worked and all I get is a blank cell (not the coloured one I am looking for).
Any help would be greatly appreciated.