Kevin

Errors out on this line most likely..........

ActiveCell.Offset(-9, 0).Range("A1").Select

If the activecell is in above row 10 this will throw an error because you are
trying to select from 9 rows above the activecell.

The -9 moves up 9 rows, the 0 means column remains unchanged.

What your macro is doing is selecting a cell 9 rows above the active cell then
copying the format of that cell and pasting into the active cell then
selecting a cell 8 rows above.

What are your exact needs? Perhaps a simpler macro can be written.


Gord Dibben Excel MVP

On Mon, 9 May 2005 10:49:01 -0700, kevinm
wrote:

Gord,
I managed to get the 'Stop Recording' popup back up, I have recorded a macro
with relative references enabled BUT I get a VB error when I run it ..

"Run-time error '1004':
Application-defined or object-defined error."

If I record a macro with relative references turned off VB doesn't give me
the error.

Here are the details on the macro which I recorded:


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 09/05/2005 by UBV2000
'

'
ActiveCell.Offset(-9, 0).Range("A1").Select
Selection.Copy
ActiveCell.Offset(9, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveCell.Offset(-8, 0).Range("A1").Select
End Sub


any suggestions on how I could avoid this error would be greatly appreciated,

thanks,

Kevin







"kevinm" wrote:

Hi Gord,

unfortunately I dont seem to be able to get the Stop Recording Toolbar to
pop up.

I am certain that it used to pop up but for some reason it is no longer
doing this. I have been forced to stop the recording by going into
Tools/Macro/Stop Recordiong.

Do you know how I go about enabling that Stop Recording popup?

thanks,

Kevin


"Gord Dibben" wrote:

Kevin

When you go to ToolsMacroRecord New Macro and OK, the Stop Recording Toolbar
should pop up.

On this Toolbar is a "relative reference" toggle button.

Record your macro with relative references turned on.


Gord Dibben Excel MVP

On Sun, 8 May 2005 09:00:01 -0700, kevinm
wrote:

Hi Paul,

thanks for your suggestion. however I have tried creating a macro but
couldn't get it to do what I want. I had problems in that I could only manage
to get Excel to create a macro that used absolute cell refereences, so when I
went to a completely different cell and ran the macro it copied from the same
source cell every time. Somehow I need to get Excel to build a macro with
relative cell references, then it might work. I wil read up on macros again
and try to figure it out,

Kevin

"paul" wrote:

i think you will need a simple macro to do this ie paste
special-formats,paste special value
--
hope this helps
Paul


"kevinm" wrote:

I have two worksheets, I have entered the formula for a cell on the 2nd
worksheet so that the cell contents are copied from a cell on the first
worksheet.

e.g. =(Sheet1!B1)

When I update the contents of the cell on the first worksheet the contents
of the cell on the 2nd sheet are updated automatically .. so far so good ..

Now, what I really want to do is have the format of the cell on the first
sheet (and NOT it's contents) copied to the cell on the 2nd worksheet. When I
change the format of the cell on the 1st worksheet I want the cell on the 2nd
sheet to update automatically.

The reason for needing this behaviour is as follows:

Sheet 1 is used to represent a collection of signal pins of a computer chip
I am working on. These signals can be grouped together according to similar
function and I color format these according their function. (there are about
600 pins which are grouped by function and represented by about ten different
colors).

Sheet2 is a physical view of the computer chip. I want it to be able to
reassign signals on the first sheet and have Excel automatically update the
color format of the cells on the second sheet. This way I can quickly see how
changing the pin assignment is reflected in the physical view of the chip.

e.g.

Sheet 1, A1 is a signal named 'A', it is formatted BLUE
Sheet 2, AA10 shows the format of cell A1 on sheet1, i.e. BLUE

Suppose I decide to change A1 on sheet1 to a different signal (B), but this
signal is of type RED. What I want to see is Excel automatically update AA10
on sheet2 so that its format is RED.

Is it possible to do this in Excel?

thanks,

Kevin