It could be that the active cell is not in row 10 or below, but in row 1-9.
For example, if you are in cell B3, an offset of -9 rows is not possible.

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