Copy cell format to cell on another worksht and update automatical

kevinm · 05-06-2005, 07:06 PM

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

paul · 05-08-2005, 01:06 AM

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
>

kevinm · 05-08-2005, 12:06 PM

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
> >

Gord Dibben · 05-08-2005, 01:06 PM

Kevin

When you go to Tools>Macro>Record 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 <[email protected]>
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
>> >

kevinm · 05-09-2005, 01:06 PM

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 Tools>Macro>Record 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 <[email protected]>
> 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
> >> >
>
>

kevinm · 05-09-2005, 02:06 PM

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 Tools>Macro>Record 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 <[email protected]>
> > 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
> > >> >
> >
> >

Myrna Larson · 05-09-2005, 04:06 PM

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 <[email protected]>
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 Tools>Macro>Record 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
<[email protected]>
>> > 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
>> > >> >
>> >
>> >

Gord Dibben · 05-09-2005, 04:06 PM

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 <[email protected]>
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 Tools>Macro>Record 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 <[email protected]>
>> > 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
>> > >> >
>> >
>> >

kevinm · 05-09-2005, 07:06 PM

Gord/Myrna,

thanks for your help, I went back and recreated the macro, I don't know what
I did different but it is working now.

All I need to figure out now is how to repeat the macro operation a number
of times.

Consider:

My master worksheet has about 600 cells, all in one column. Of these a
number of them are grouped into 20 consecutive cells. Instead of executing
the macro twenty times to copy the 20 cells to worksheet 2, is there a way of
looping the macro a user specified number of times?

Let me know if I should start a new thread for this,

thanks,

Kevin

"Gord Dibben" wrote:

> 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 <[email protected]>
> 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 Tools>Macro>Record 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 <[email protected]>
> >> > 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
> >> > >> >
> >> >
> >> >
>
>

kevinm · 05-09-2005, 09:06 PM

Hi experts,
thanks to your help I am having 'some' success with macros but I am finding
their behaviour somewhat unpredictable.

Rather then debug this one problem at a time and have to keep pestering you
for help each time I thought I should give you the full picture of what I am
trying to accomplish here. Maybe what I am trying to do is beyond the
capabilities of Excel, if so I would like to know so that I don't need to
keep hassling you for help with something which at the end of the day Excel
might not be able to handle.

Ultimately I want my spreadsheet to consist of two worksheets, sheet1 would
contain column D which corresponds to a list of pins for a computer chip I am
working on.

Sheet2 would be an array of rows and columns representing the physical view
of the computer chip which I am working on. This would essentially be a
square array of pins. Each of these pins would be numbered, i.e. A1, A2,etc,
much like an array of rows and columns appears on a spreadsheet.

Column D on sheet1 would contain a list of values, pointing to a cell on
sheet2 ..

Example1:

D2, value = C17
D3, value = D17
D4, value = C20

etc, etc

(in total there would be 448 rows of D to be parsed (D2:D449))

Each of these D cells would be colored by the user according to their
electrical function.

What I want to do is read the value of each D cell and use it's contents
(e.g. 'C17') as a pointer to the corresponding cell on sheet2, (i.e. C17,
D17, etc) then I want to do a copy/paste special to copy the format info
(i.e. color) to the cells on sheet2. I want to do this for all D cells within
the range D2:D449.

The pin array would be 26x32, this would be represented on sheet2 as 26 rows
by 32 columns.

I want this to all be automated, which I assume will require some huge macro
to be created.

could you please advise if Excel would be capable of doing this?

thanks,

Kevin

"kevinm" wrote:

> Gord/Myrna,
>
> thanks for your help, I went back and recreated the macro, I don't know what
> I did different but it is working now.
>
> All I need to figure out now is how to repeat the macro operation a number
> of times.
>
> Consider:
>
> My master worksheet has about 600 cells, all in one column. Of these a
> number of them are grouped into 20 consecutive cells. Instead of executing
> the macro twenty times to copy the 20 cells to worksheet 2, is there a way of
> looping the macro a user specified number of times?
>
> Let me know if I should start a new thread for this,
>
> thanks,
>
> Kevin
>
>
> "Gord Dibben" wrote:
>
> > 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 <[email protected]>
> > 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 Tools>Macro>Record 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 <[email protected]>
> > >> > 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
> > >> > >> >
> > >> >
> > >> >
> >
> >

Gord Dibben · 05-09-2005, 09:06 PM

Kevin

Still not sure what your needs are. Copy cells or copy formats?

Also "a number of them are grouped into 20 consecutive cells".

How many and what is the criterion for deciding which 20 to copy?

In blocks of 20 cells to where?

This macro will copy cells only to another worksheet.

Sub move20()
ActiveCell.Resize(20, 1).Copy Destination:=Worksheets("Sheet2").Range("A1")
End Sub

Probably not what you want, but a start.

No need for a new thread unless you want to post over in the excel.programming
group, but everyone over there reads this group also.

Gord

On Mon, 9 May 2005 15:10:07 -0700, kevinm <[email protected]>
wrote:

>Gord/Myrna,
>
>thanks for your help, I went back and recreated the macro, I don't know what
>I did different but it is working now.
>
>All I need to figure out now is how to repeat the macro operation a number
>of times.
>
>Consider:
>
>My master worksheet has about 600 cells, all in one column. Of these a
>number of them are grouped into 20 consecutive cells. Instead of executing
>the macro twenty times to copy the 20 cells to worksheet 2, is there a way of
>looping the macro a user specified number of times?
>
>Let me know if I should start a new thread for this,
>
>thanks,
>
>Kevin
>
>
>"Gord Dibben" wrote:
>
>> 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 <[email protected]>
>> 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 Tools>Macro>Record 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 <[email protected]>
>> >> > 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
>> >> > >> >
>> >> >
>> >> >
>>
>>

kevinm · 05-10-2005, 12:06 PM

Hi Gord,

sorry I am doing a poor job explaining my requirements, it is difficult
without actually sending you an example spreadsheet. Let me have another go
at explaining, consider this simplified example:

Sheet1 looks something like:

Column

A B C D E F
Row 1 C3 test Red_Cell
Row 2 D2 test Blue_Cell
Row 3 A4 test Green_Cell
Row 4 B3 test Pink_Cell

Initially Sheet2 looks like:

Column

A B C D E F
Row 1
Row 2
Row 3
Row 4

I would like a macro which work something like:

Step1. Read cell value for first item in Sheet1 column A (A1 value = "C3")
Step2. Check the color format of the cell two columns to the right
(colored RED)
Step3. Go to Sheet2, color the cell whose reference was determined in
Step1 (C3) the color identified in Step2.
Step4. Read the cell value for the second item in Sheet1 column A (A2
value = "D2")
Step5. Check the color format of the cell two columns to the right
(colored BLUE)
Step6. Go to Sheet2, color the cell whose reference was determined in
Step4 (D2) the color identified in Step5.
Step7. Read the cell value for the third item in Sheet1 column A (A2 value
= "A4")
etc,
etc

When the macro has finished Sheet2 should look like:

Column

A B C D E F
Row 1
Row 2 (blue)
Row 3 (pink) (red)
Row 4 (green)

Where (xxx) is the color fill of the cell, not the value for the cell
contents.

I hope that my explanation is a little clearer this time,

Kevin

"Gord Dibben" wrote:

> Kevin
>
> Still not sure what your needs are. Copy cells or copy formats?
>
> Also "a number of them are grouped into 20 consecutive cells".
>
> How many and what is the criterion for deciding which 20 to copy?
>
> In blocks of 20 cells to where?
>
> This macro will copy cells only to another worksheet.
>
> Sub move20()
> ActiveCell.Resize(20, 1).Copy Destination:=Worksheets("Sheet2").Range("A1")
> End Sub
>
> Probably not what you want, but a start.
>
> No need for a new thread unless you want to post over in the excel.programming
> group, but everyone over there reads this group also.
>
>
> Gord
>
>
> On Mon, 9 May 2005 15:10:07 -0700, kevinm <[email protected]>
> wrote:
>
> >Gord/Myrna,
> >
> >thanks for your help, I went back and recreated the macro, I don't know what
> >I did different but it is working now.
> >
> >All I need to figure out now is how to repeat the macro operation a number
> >of times.
> >
> >Consider:
> >
> >My master worksheet has about 600 cells, all in one column. Of these a
> >number of them are grouped into 20 consecutive cells. Instead of executing
> >the macro twenty times to copy the 20 cells to worksheet 2, is there a way of
> >looping the macro a user specified number of times?
> >
> >Let me know if I should start a new thread for this,
> >
> >thanks,
> >
> >Kevin
> >
> >
> >"Gord Dibben" wrote:
> >
> >> 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 <[email protected]>
> >> 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 Tools>Macro>Record 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 <[email protected]>
> >> >> > 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
> >> >> > >> >
> >> >> >
> >> >> >
> >>
> >>
>
>

Gord Dibben · 05-10-2005, 02:06 PM

Kevin

I'd have to look at this one for a while. VBA is not my strong suit.

Stick around here and wait for someone(there are many) with greater skills
than myself.

Meantime, I'll try to work on it between Tee-Times and other projects.

Gord

On Tue, 10 May 2005 09:01:08 -0700, kevinm <[email protected]>
wrote:

>Hi Gord,
>
>sorry I am doing a poor job explaining my requirements, it is difficult
>without actually sending you an example spreadsheet. Let me have another go
>at explaining, consider this simplified example:
>
>
>
>Sheet1 looks something like:
>
> Column
>
> A B C D E F
>Row 1 C3 test Red_Cell
>Row 2 D2 test Blue_Cell
>Row 3 A4 test Green_Cell
>Row 4 B3 test Pink_Cell
>
>
>
>
>Initially Sheet2 looks like:
>
>
> Column
>
> A B C D E F
>Row 1
>Row 2
>Row 3
>Row 4
>
>
>
>I would like a macro which work something like:
>
>Step1. Read cell value for first item in Sheet1 column A (A1 value = "C3")
>Step2. Check the color format of the cell two columns to the right
>(colored RED)
>Step3. Go to Sheet2, color the cell whose reference was determined in
>Step1 (C3) the color identified in Step2.
>Step4. Read the cell value for the second item in Sheet1 column A (A2
>value = "D2")
>Step5. Check the color format of the cell two columns to the right
>(colored BLUE)
>Step6. Go to Sheet2, color the cell whose reference was determined in
>Step4 (D2) the color identified in Step5.
>Step7. Read the cell value for the third item in Sheet1 column A (A2 value
>= "A4")
>etc,
>etc
>
>
>When the macro has finished Sheet2 should look like:
>
>
> Column
>
> A B C D E F
>Row 1
>Row 2 (blue)
>Row 3 (pink) (red)
>Row 4 (green)
>
>Where (xxx) is the color fill of the cell, not the value for the cell
>contents.
>
>
>I hope that my explanation is a little clearer this time,
>
>Kevin
>
>
>
>"Gord Dibben" wrote:
>
>> Kevin
>>
>> Still not sure what your needs are. Copy cells or copy formats?
>>
>> Also "a number of them are grouped into 20 consecutive cells".
>>
>> How many and what is the criterion for deciding which 20 to copy?
>>
>> In blocks of 20 cells to where?
>>
>> This macro will copy cells only to another worksheet.
>>
>> Sub move20()
>> ActiveCell.Resize(20, 1).Copy Destination:=Worksheets("Sheet2").Range("A1")
>> End Sub
>>
>> Probably not what you want, but a start.
>>
>> No need for a new thread unless you want to post over in the excel.programming
>> group, but everyone over there reads this group also.
>>
>>
>> Gord
>>
>>
>> On Mon, 9 May 2005 15:10:07 -0700, kevinm <[email protected]>
>> wrote:
>>
>> >Gord/Myrna,
>> >
>> >thanks for your help, I went back and recreated the macro, I don't know what
>> >I did different but it is working now.
>> >
>> >All I need to figure out now is how to repeat the macro operation a number
>> >of times.
>> >
>> >Consider:
>> >
>> >My master worksheet has about 600 cells, all in one column. Of these a
>> >number of them are grouped into 20 consecutive cells. Instead of executing
>> >the macro twenty times to copy the 20 cells to worksheet 2, is there a way of
>> >looping the macro a user specified number of times?
>> >
>> >Let me know if I should start a new thread for this,
>> >
>> >thanks,
>> >
>> >Kevin
>> >
>> >
>> >"Gord Dibben" wrote:
>> >
>> >> 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 <[email protected]>
>> >> 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 Tools>Macro>Record 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 <[email protected]>
>> >> >> > 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
>> >> >> > >> >
>> >> >> >
>> >> >> >
>> >>
>> >>
>>
>>

kevinm · 05-10-2005, 03:06 PM

Gord,

I am glad you understand my requirements now. It sounds so simple to do when
I explain it in simple terms but I haven't been able to figure it out.

If it is not possible I will just have to create a huge VBA file with a
separate procedure defined for every cell in column A, this is going to be
extremely tedious and error prone. In the real spreadsheet column A has about
600 rows to process!

If you (or anyone else reading watching this forum) can figure it out it
will save me an awful lot of time. Fingers crossed ..

Kevin

"Gord Dibben" wrote:

> Kevin
>
> I'd have to look at this one for a while. VBA is not my strong suit.
>
> Stick around here and wait for someone(there are many) with greater skills
> than myself.
>
> Meantime, I'll try to work on it between Tee-Times and other projects.
>
>
> Gord
>
> On Tue, 10 May 2005 09:01:08 -0700, kevinm <[email protected]>
> wrote:
>
> >Hi Gord,
> >
> >sorry I am doing a poor job explaining my requirements, it is difficult
> >without actually sending you an example spreadsheet. Let me have another go
> >at explaining, consider this simplified example:
> >
> >
> >
> >Sheet1 looks something like:
> >
> > Column
> >
> > A B C D E F
> >Row 1 C3 test Red_Cell
> >Row 2 D2 test Blue_Cell
> >Row 3 A4 test Green_Cell
> >Row 4 B3 test Pink_Cell
> >
> >
> >
> >
> >Initially Sheet2 looks like:
> >
> >
> > Column
> >
> > A B C D E F
> >Row 1
> >Row 2
> >Row 3
> >Row 4
> >
> >
> >
> >I would like a macro which work something like:
> >
> >Step1. Read cell value for first item in Sheet1 column A (A1 value = "C3")
> >Step2. Check the color format of the cell two columns to the right
> >(colored RED)
> >Step3. Go to Sheet2, color the cell whose reference was determined in
> >Step1 (C3) the color identified in Step2.
> >Step4. Read the cell value for the second item in Sheet1 column A (A2
> >value = "D2")
> >Step5. Check the color format of the cell two columns to the right
> >(colored BLUE)
> >Step6. Go to Sheet2, color the cell whose reference was determined in
> >Step4 (D2) the color identified in Step5.
> >Step7. Read the cell value for the third item in Sheet1 column A (A2 value
> >= "A4")
> >etc,
> >etc
> >
> >
> >When the macro has finished Sheet2 should look like:
> >
> >
> > Column
> >
> > A B C D E F
> >Row 1
> >Row 2 (blue)
> >Row 3 (pink) (red)
> >Row 4 (green)
> >
> >Where (xxx) is the color fill of the cell, not the value for the cell
> >contents.
> >
> >
> >I hope that my explanation is a little clearer this time,
> >
> >Kevin
> >
> >
> >
> >"Gord Dibben" wrote:
> >
> >> Kevin
> >>
> >> Still not sure what your needs are. Copy cells or copy formats?
> >>
> >> Also "a number of them are grouped into 20 consecutive cells".
> >>
> >> How many and what is the criterion for deciding which 20 to copy?
> >>
> >> In blocks of 20 cells to where?
> >>
> >> This macro will copy cells only to another worksheet.
> >>
> >> Sub move20()
> >> ActiveCell.Resize(20, 1).Copy Destination:=Worksheets("Sheet2").Range("A1")
> >> End Sub
> >>
> >> Probably not what you want, but a start.
> >>
> >> No need for a new thread unless you want to post over in the excel.programming
> >> group, but everyone over there reads this group also.
> >>
> >>
> >> Gord
> >>
> >>
> >> On Mon, 9 May 2005 15:10:07 -0700, kevinm <[email protected]>
> >> wrote:
> >>
> >> >Gord/Myrna,
> >> >
> >> >thanks for your help, I went back and recreated the macro, I don't know what
> >> >I did different but it is working now.
> >> >
> >> >All I need to figure out now is how to repeat the macro operation a number
> >> >of times.
> >> >
> >> >Consider:
> >> >
> >> >My master worksheet has about 600 cells, all in one column. Of these a
> >> >number of them are grouped into 20 consecutive cells. Instead of executing
> >> >the macro twenty times to copy the 20 cells to worksheet 2, is there a way of
> >> >looping the macro a user specified number of times?
> >> >
> >> >Let me know if I should start a new thread for this,
> >> >
> >> >thanks,
> >> >
> >> >Kevin
> >> >
> >> >
> >> >"Gord Dibben" wrote:
> >> >
> >> >> 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 <[email protected]>
> >> >> 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 Tools>Macro>Record 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 <[email protected]>
> >> >> >> > 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.

paul · 05-11-2005, 07:06 AM

How about this for an idea.Have a custom worksheet function called say colour
where a a cell (or selected range?) is shaded in relation to a certain
value.I had a go using this code but it didnt like it
Function colour()
colour = Cells.Interior.
.ColorIndex = 1
.Pattern = xlSolid
End Function
=colour(1) would result in the selected cell or cells being shaded in black
the values on sheet 1 could then be linked to the cells on sheet two to give
the result kevin wants.I think perhaps it could be a function that would be
more flexible than conditional formatting.

--
paul
remove nospam for email addy!

"kevinm" wrote:

> Gord,
>
> I am glad you understand my requirements now. It sounds so simple to do when
> I explain it in simple terms but I haven't been able to figure it out.
>
> If it is not possible I will just have to create a huge VBA file with a
> separate procedure defined for every cell in column A, this is going to be
> extremely tedious and error prone. In the real spreadsheet column A has about
> 600 rows to process!
>
> If you (or anyone else reading watching this forum) can figure it out it
> will save me an awful lot of time. Fingers crossed ..
>
> Kevin
>
>
> "Gord Dibben" wrote:
>
> > Kevin
> >
> > I'd have to look at this one for a while. VBA is not my strong suit.
> >
> > Stick around here and wait for someone(there are many) with greater skills
> > than myself.
> >
> > Meantime, I'll try to work on it between Tee-Times and other projects.
> >
> >
> > Gord
> >
> > On Tue, 10 May 2005 09:01:08 -0700, kevinm <[email protected]>
> > wrote:
> >
> > >Hi Gord,
> > >
> > >sorry I am doing a poor job explaining my requirements, it is difficult
> > >without actually sending you an example spreadsheet. Let me have another go
> > >at explaining, consider this simplified example:
> > >
> > >
> > >
> > >Sheet1 looks something like:
> > >
> > > Column
> > >
> > > A B C D E F
> > >Row 1 C3 test Red_Cell
> > >Row 2 D2 test Blue_Cell
> > >Row 3 A4 test Green_Cell
> > >Row 4 B3 test Pink_Cell
> > >
> > >
> > >
> > >
> > >Initially Sheet2 looks like:
> > >
> > >
> > > Column
> > >
> > > A B C D E F
> > >Row 1
> > >Row 2
> > >Row 3
> > >Row 4
> > >
> > >
> > >
> > >I would like a macro which work something like:
> > >
> > >Step1. Read cell value for first item in Sheet1 column A (A1 value = "C3")
> > >Step2. Check the color format of the cell two columns to the right
> > >(colored RED)
> > >Step3. Go to Sheet2, color the cell whose reference was determined in
> > >Step1 (C3) the color identified in Step2.
> > >Step4. Read the cell value for the second item in Sheet1 column A (A2
> > >value = "D2")
> > >Step5. Check the color format of the cell two columns to the right
> > >(colored BLUE)
> > >Step6. Go to Sheet2, color the cell whose reference was determined in
> > >Step4 (D2) the color identified in Step5.
> > >Step7. Read the cell value for the third item in Sheet1 column A (A2 value
> > >= "A4")
> > >etc,
> > >etc
> > >
> > >
> > >When the macro has finished Sheet2 should look like:
> > >
> > >
> > > Column
> > >
> > > A B C D E F
> > >Row 1
> > >Row 2 (blue)
> > >Row 3 (pink) (red)
> > >Row 4 (green)
> > >
> > >Where (xxx) is the color fill of the cell, not the value for the cell
> > >contents.
> > >
> > >
> > >I hope that my explanation is a little clearer this time,
> > >
> > >Kevin
> > >
> > >
> > >
> > >"Gord Dibben" wrote:
> > >
> > >> Kevin
> > >>
> > >> Still not sure what your needs are. Copy cells or copy formats?
> > >>
> > >> Also "a number of them are grouped into 20 consecutive cells".
> > >>
> > >> How many and what is the criterion for deciding which 20 to copy?
> > >>
> > >> In blocks of 20 cells to where?
> > >>
> > >> This macro will copy cells only to another worksheet.
> > >>
> > >> Sub move20()
> > >> ActiveCell.Resize(20, 1).Copy Destination:=Worksheets("Sheet2").Range("A1")
> > >> End Sub
> > >>
> > >> Probably not what you want, but a start.
> > >>
> > >> No need for a new thread unless you want to post over in the excel.programming
> > >> group, but everyone over there reads this group also.
> > >>
> > >>
> > >> Gord
> > >>
> > >>
> > >> On Mon, 9 May 2005 15:10:07 -0700, kevinm <[email protected]>
> > >> wrote:
> > >>
> > >> >Gord/Myrna,
> > >> >
> > >> >thanks for your help, I went back and recreated the macro, I don't know what
> > >> >I did different but it is working now.
> > >> >
> > >> >All I need to figure out now is how to repeat the macro operation a number
> > >> >of times.
> > >> >
> > >> >Consider:
> > >> >
> > >> >My master worksheet has about 600 cells, all in one column. Of these a
> > >> >number of them are grouped into 20 consecutive cells. Instead of executing
> > >> >the macro twenty times to copy the 20 cells to worksheet 2, is there a way of
> > >> >looping the macro a user specified number of times?
> > >> >
> > >> >Let me know if I should start a new thread for this,
> > >> >
> > >> >thanks,
> > >> >
> > >> >Kevin
> > >> >
> > >> >
> > >> >"Gord Dibben" wrote:
> > >> >
> > >> >> 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 <[email protected]>
> > >> >> 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 Tools>Macro>Record 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 <[email protected]>
> > >> >> >> > 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

kevinm · 05-11-2005, 05:06 PM

Hi Paul,

thanks for the suggestion, I am certainly willing to give is a try but how
do I create this function, is it just another macro? Do I have to use the VB
editor to type in the function by hand?

sorry for the stupid questions, I have not been to this depth with Excel
before,

Kevin

"paul" wrote:

> How about this for an idea.Have a custom worksheet function called say colour
> where a a cell (or selected range?) is shaded in relation to a certain
> value.I had a go using this code but it didnt like it
> Function colour()
> colour = Cells.Interior.
> .ColorIndex = 1
> .Pattern = xlSolid
> End Function
> =colour(1) would result in the selected cell or cells being shaded in black
> the values on sheet 1 could then be linked to the cells on sheet two to give
> the result kevin wants.I think perhaps it could be a function that would be
> more flexible than conditional formatting.
>
>
> --
> paul
> remove nospam for email addy!
>
>
>
> "kevinm" wrote:
>
> > Gord,
> >
> > I am glad you understand my requirements now. It sounds so simple to do when
> > I explain it in simple terms but I haven't been able to figure it out.
> >
> > If it is not possible I will just have to create a huge VBA file with a
> > separate procedure defined for every cell in column A, this is going to be
> > extremely tedious and error prone. In the real spreadsheet column A has about
> > 600 rows to process!
> >
> > If you (or anyone else reading watching this forum) can figure it out it
> > will save me an awful lot of time. Fingers crossed ..
> >
> > Kevin
> >
> >
> > "Gord Dibben" wrote:
> >
> > > Kevin
> > >
> > > I'd have to look at this one for a while. VBA is not my strong suit.
> > >
> > > Stick around here and wait for someone(there are many) with greater skills
> > > than myself.
> > >
> > > Meantime, I'll try to work on it between Tee-Times and other projects.
> > >
> > >
> > > Gord
> > >
> > > On Tue, 10 May 2005 09:01:08 -0700, kevinm <[email protected]>
> > > wrote:
> > >
> > > >Hi Gord,
> > > >
> > > >sorry I am doing a poor job explaining my requirements, it is difficult
> > > >without actually sending you an example spreadsheet. Let me have another go
> > > >at explaining, consider this simplified example:
> > > >
> > > >
> > > >
> > > >Sheet1 looks something like:
> > > >
> > > > Column
> > > >
> > > > A B C D E F
> > > >Row 1 C3 test Red_Cell
> > > >Row 2 D2 test Blue_Cell
> > > >Row 3 A4 test Green_Cell
> > > >Row 4 B3 test Pink_Cell
> > > >
> > > >
> > > >
> > > >
> > > >Initially Sheet2 looks like:
> > > >
> > > >
> > > > Column
> > > >
> > > > A B C D E F
> > > >Row 1
> > > >Row 2
> > > >Row 3
> > > >Row 4
> > > >
> > > >
> > > >
> > > >I would like a macro which work something like:
> > > >
> > > >Step1. Read cell value for first item in Sheet1 column A (A1 value = "C3")
> > > >Step2. Check the color format of the cell two columns to the right
> > > >(colored RED)
> > > >Step3. Go to Sheet2, color the cell whose reference was determined in
> > > >Step1 (C3) the color identified in Step2.
> > > >Step4. Read the cell value for the second item in Sheet1 column A (A2
> > > >value = "D2")
> > > >Step5. Check the color format of the cell two columns to the right
> > > >(colored BLUE)
> > > >Step6. Go to Sheet2, color the cell whose reference was determined in
> > > >Step4 (D2) the color identified in Step5.
> > > >Step7. Read the cell value for the third item in Sheet1 column A (A2 value
> > > >= "A4")
> > > >etc,
> > > >etc
> > > >
> > > >
> > > >When the macro has finished Sheet2 should look like:
> > > >
> > > >
> > > > Column
> > > >
> > > > A B C D E F
> > > >Row 1
> > > >Row 2 (blue)
> > > >Row 3 (pink) (red)
> > > >Row 4 (green)
> > > >
> > > >Where (xxx) is the color fill of the cell, not the value for the cell
> > > >contents.
> > > >
> > > >
> > > >I hope that my explanation is a little clearer this time,
> > > >
> > > >Kevin
> > > >
> > > >
> > > >
> > > >"Gord Dibben" wrote:
> > > >
> > > >> Kevin
> > > >>
> > > >> Still not sure what your needs are. Copy cells or copy formats?
> > > >>
> > > >> Also "a number of them are grouped into 20 consecutive cells".
> > > >>
> > > >> How many and what is the criterion for deciding which 20 to copy?
> > > >>
> > > >> In blocks of 20 cells to where?
> > > >>
> > > >> This macro will copy cells only to another worksheet.
> > > >>
> > > >> Sub move20()
> > > >> ActiveCell.Resize(20, 1).Copy Destination:=Worksheets("Sheet2").Range("A1")
> > > >> End Sub
> > > >>
> > > >> Probably not what you want, but a start.
> > > >>
> > > >> No need for a new thread unless you want to post over in the excel.programming
> > > >> group, but everyone over there reads this group also.
> > > >>
> > > >>
> > > >> Gord
> > > >>
> > > >>
> > > >> On Mon, 9 May 2005 15:10:07 -0700, kevinm <[email protected]>
> > > >> wrote:
> > > >>
> > > >> >Gord/Myrna,
> > > >> >
> > > >> >thanks for your help, I went back and recreated the macro, I don't know what
> > > >> >I did different but it is working now.
> > > >> >
> > > >> >All I need to figure out now is how to repeat the macro operation a number
> > > >> >of times.
> > > >> >
> > > >> >Consider:
> > > >> >
> > > >> >My master worksheet has about 600 cells, all in one column. Of these a
> > > >> >number of them are grouped into 20 consecutive cells. Instead of executing
> > > >> >the macro twenty times to copy the 20 cells to worksheet 2, is there a way of
> > > >> >looping the macro a user specified number of times?
> > > >> >
> > > >> >Let me know if I should start a new thread for this,
> > > >> >
> > > >> >thanks,
> > > >> >
> > > >> >Kevin
> > > >> >
> > > >> >
> > > >> >"Gord Dibben" wrote:
> > > >> >
> > > >> >> 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 <[email protected]>
> > > >> >> 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 Tools>Macro>Record 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 <[email protected]>
> > > >> >> >> > 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 · 05-11-2005, 06:06 PM

sorry kevin i cant help.I did a wee bit more research and from what i have
seen custom functions do not extend to formatting.Custom funcions are a kind
of macro.
My only suggestion is either wait for gordon to respond or email him and
tell him that you will make a new post in progamming.In that post ask if a
custom function will do the job of copying or linking formats,or if a macro
is required.Are your pin diagrams always the same size or do they vary ?ie
one pin will be 2r x 5c and another 2r x 10c and even bigger???

--
paul
remove nospam for email addy!

"kevinm" wrote:

> Hi Paul,
>
> thanks for the suggestion, I am certainly willing to give is a try but how
> do I create this function, is it just another macro? Do I have to use the VB
> editor to type in the function by hand?
>
> sorry for the stupid questions, I have not been to this depth with Excel
> before,
>
> Kevin
>
>
> "paul" wrote:
>
> > How about this for an idea.Have a custom worksheet function called say colour
> > where a a cell (or selected range?) is shaded in relation to a certain
> > value.I had a go using this code but it didnt like it
> > Function colour()
> > colour = Cells.Interior.
> > .ColorIndex = 1
> > .Pattern = xlSolid
> > End Function
> > =colour(1) would result in the selected cell or cells being shaded in black
> > the values on sheet 1 could then be linked to the cells on sheet two to give
> > the result kevin wants.I think perhaps it could be a function that would be
> > more flexible than conditional formatting.
> >
> >
> > --
> > paul
> > remove nospam for email addy!
> >
> >
> >
> > "kevinm" wrote:
> >
> > > Gord,
> > >
> > > I am glad you understand my requirements now. It sounds so simple to do when
> > > I explain it in simple terms but I haven't been able to figure it out.
> > >
> > > If it is not possible I will just have to create a huge VBA file with a
> > > separate procedure defined for every cell in column A, this is going to be
> > > extremely tedious and error prone. In the real spreadsheet column A has about
> > > 600 rows to process!
> > >
> > > If you (or anyone else reading watching this forum) can figure it out it
> > > will save me an awful lot of time. Fingers crossed ..
> > >
> > > Kevin
> > >
> > >
> > > "Gord Dibben" wrote:
> > >
> > > > Kevin
> > > >
> > > > I'd have to look at this one for a while. VBA is not my strong suit.
> > > >
> > > > Stick around here and wait for someone(there are many) with greater skills
> > > > than myself.
> > > >
> > > > Meantime, I'll try to work on it between Tee-Times and other projects.
> > > >
> > > >
> > > > Gord
> > > >
> > > > On Tue, 10 May 2005 09:01:08 -0700, kevinm <[email protected]>
> > > > wrote:
> > > >
> > > > >Hi Gord,
> > > > >
> > > > >sorry I am doing a poor job explaining my requirements, it is difficult
> > > > >without actually sending you an example spreadsheet. Let me have another go
> > > > >at explaining, consider this simplified example:
> > > > >
> > > > >
> > > > >
> > > > >Sheet1 looks something like:
> > > > >
> > > > > Column
> > > > >
> > > > > A B C D E F
> > > > >Row 1 C3 test Red_Cell
> > > > >Row 2 D2 test Blue_Cell
> > > > >Row 3 A4 test Green_Cell
> > > > >Row 4 B3 test Pink_Cell
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >Initially Sheet2 looks like:
> > > > >
> > > > >
> > > > > Column
> > > > >
> > > > > A B C D E F
> > > > >Row 1
> > > > >Row 2
> > > > >Row 3
> > > > >Row 4
> > > > >
> > > > >
> > > > >
> > > > >I would like a macro which work something like:
> > > > >
> > > > >Step1. Read cell value for first item in Sheet1 column A (A1 value = "C3")
> > > > >Step2. Check the color format of the cell two columns to the right
> > > > >(colored RED)
> > > > >Step3. Go to Sheet2, color the cell whose reference was determined in
> > > > >Step1 (C3) the color identified in Step2.
> > > > >Step4. Read the cell value for the second item in Sheet1 column A (A2
> > > > >value = "D2")
> > > > >Step5. Check the color format of the cell two columns to the right
> > > > >(colored BLUE)
> > > > >Step6. Go to Sheet2, color the cell whose reference was determined in
> > > > >Step4 (D2) the color identified in Step5.
> > > > >Step7. Read the cell value for the third item in Sheet1 column A (A2 value
> > > > >= "A4")
> > > > >etc,
> > > > >etc
> > > > >
> > > > >
> > > > >When the macro has finished Sheet2 should look like:
> > > > >
> > > > >
> > > > > Column
> > > > >
> > > > > A B C D E F
> > > > >Row 1
> > > > >Row 2 (blue)
> > > > >Row 3 (pink) (red)
> > > > >Row 4 (green)
> > > > >
> > > > >Where (xxx) is the color fill of the cell, not the value for the cell
> > > > >contents.
> > > > >
> > > > >
> > > > >I hope that my explanation is a little clearer this time,
> > > > >
> > > > >Kevin
> > > > >
> > > > >
> > > > >
> > > > >"Gord Dibben" wrote:
> > > > >
> > > > >> Kevin
> > > > >>
> > > > >> Still not sure what your needs are. Copy cells or copy formats?
> > > > >>
> > > > >> Also "a number of them are grouped into 20 consecutive cells".
> > > > >>
> > > > >> How many and what is the criterion for deciding which 20 to copy?
> > > > >>
> > > > >> In blocks of 20 cells to where?
> > > > >>
> > > > >> This macro will copy cells only to another worksheet.
> > > > >>
> > > > >> Sub move20()
> > > > >> ActiveCell.Resize(20, 1).Copy Destination:=Worksheets("Sheet2").Range("A1")
> > > > >> End Sub
> > > > >>
> > > > >> Probably not what you want, but a start.
> > > > >>
> > > > >> No need for a new thread unless you want to post over in the excel.programming
> > > > >> group, but everyone over there reads this group also.
> > > > >>
> > > > >>
> > > > >> Gord
> > > > >>
> > > > >>
> > > > >> On Mon, 9 May 2005 15:10:07 -0700, kevinm <[email protected]>
> > > > >> wrote:
> > > > >>
> > > > >> >Gord/Myrna,
> > > > >> >
> > > > >> >thanks for your help, I went back and recreated the macro, I don't know what
> > > > >> >I did different but it is working now.
> > > > >> >
> > > > >> >All I need to figure out now is how to repeat the macro operation a number
> > > > >> >of times.
> > > > >> >
> > > > >> >Consider:
> > > > >> >
> > > > >> >My master worksheet has about 600 cells, all in one column. Of these a
> > > > >> >number of them are grouped into 20 consecutive cells. Instead of executing
> > > > >> >the macro twenty times to copy the 20 cells to worksheet 2, is there a way of
> > > > >> >looping the macro a user specified number of times?
> > > > >> >
> > > > >> >Let me know if I should start a new thread for this,
> > > > >> >
> > > > >> >thanks,
> > > > >> >
> > > > >> >Kevin
> > > > >> >
> > > > >> >
> > > > >> >"Gord Dibben" wrote:
> > > > >> >
> > > > >> >> 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 <[email protected]>
> > > > >> >> 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 Tools>Macro>Record 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 <[email protected]>
> > > > >> >> >> > 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

Gord Dibben · 05-11-2005, 08:06 PM

Kevin

You've not been forgotten.

Been busy but will try to get you something in the next day or so.

Monday I'm starting a Consulting contract and will be out of town for most of
every week until end of September so I better get you something before then.

Gord

On Wed, 11 May 2005 13:06:11 -0700, kevinm <[email protected]>
wrote:

>Hi Paul,
>
>thanks for the suggestion, I am certainly willing to give is a try but how
>do I create this function, is it just another macro? Do I have to use the VB
>editor to type in the function by hand?
>
>sorry for the stupid questions, I have not been to this depth with Excel
>before,
>
>Kevin
>
>
>"paul" wrote:
>
>> How about this for an idea.Have a custom worksheet function called say colour
>> where a a cell (or selected range?) is shaded in relation to a certain
>> value.I had a go using this code but it didnt like it
>> Function colour()
>> colour = Cells.Interior.
>> .ColorIndex = 1
>> .Pattern = xlSolid
>> End Function
>> =colour(1) would result in the selected cell or cells being shaded in black
>> the values on sheet 1 could then be linked to the cells on sheet two to give
>> the result kevin wants.I think perhaps it could be a function that would be
>> more flexible than conditional formatting.
>>
>>
>> --
>> paul
>> remove nospam for email addy!
>>
>>
>>
>> "kevinm" wrote:
>>
>> > Gord,
>> >
>> > I am glad you understand my requirements now. It sounds so simple to do when
>> > I explain it in simple terms but I haven't been able to figure it out.
>> >
>> > If it is not possible I will just have to create a huge VBA file with a
>> > separate procedure defined for every cell in column A, this is going to be
>> > extremely tedious and error prone. In the real spreadsheet column A has about
>> > 600 rows to process!
>> >
>> > If you (or anyone else reading watching this forum) can figure it out it
>> > will save me an awful lot of time. Fingers crossed ..
>> >
>> > Kevin
>> >
>> >
>> > "Gord Dibben" wrote:
>> >
>> > > Kevin
>> > >
>> > > I'd have to look at this one for a while. VBA is not my strong suit.
>> > >
>> > > Stick around here and wait for someone(there are many) with greater skills
>> > > than myself.
>> > >
>> > > Meantime, I'll try to work on it between Tee-Times and other projects.
>> > >
>> > >
>> > > Gord
>> > >
>> > > On Tue, 10 May 2005 09:01:08 -0700, kevinm <[email protected]>
>> > > wrote:
>> > >
>> > > >Hi Gord,
>> > > >
>> > > >sorry I am doing a poor job explaining my requirements, it is difficult
>> > > >without actually sending you an example spreadsheet. Let me have another go
>> > > >at explaining, consider this simplified example:
>> > > >
>> > > >
>> > > >
>> > > >Sheet1 looks something like:
>> > > >
>> > > > Column
>> > > >
>> > > > A B C D E F
>> > > >Row 1 C3 test Red_Cell
>> > > >Row 2 D2 test Blue_Cell
>> > > >Row 3 A4 test Green_Cell
>> > > >Row 4 B3 test Pink_Cell
>> > > >
>> > > >
>> > > >
>> > > >
>> > > >Initially Sheet2 looks like:
>> > > >
>> > > >
>> > > > Column
>> > > >
>> > > > A B C D E F
>> > > >Row 1
>> > > >Row 2
>> > > >Row 3
>> > > >Row 4
>> > > >
>> > > >
>> > > >
>> > > >I would like a macro which work something like:
>> > > >
>> > > >Step1. Read cell value for first item in Sheet1 column A (A1 value = "C3")
>> > > >Step2. Check the color format of the cell two columns to the right
>> > > >(colored RED)
>> > > >Step3. Go to Sheet2, color the cell whose reference was determined in
>> > > >Step1 (C3) the color identified in Step2.
>> > > >Step4. Read the cell value for the second item in Sheet1 column A (A2
>> > > >value = "D2")
>> > > >Step5. Check the color format of the cell two columns to the right
>> > > >(colored BLUE)
>> > > >Step6. Go to Sheet2, color the cell whose reference was determined in
>> > > >Step4 (D2) the color identified in Step5.
>> > > >Step7. Read the cell value for the third item in Sheet1 column A (A2 value
>> > > >= "A4")
>> > > >etc,
>> > > >etc
>> > > >
>> > > >
>> > > >When the macro has finished Sheet2 should look like:
>> > > >
>> > > >
>> > > > Column
>> > > >
>> > > > A B C D E F
>> > > >Row 1
>> > > >Row 2 (blue)
>> > > >Row 3 (pink) (red)
>> > > >Row 4 (green)
>> > > >
>> > > >Where (xxx) is the color fill of the cell, not the value for the cell
>> > > >contents.
>> > > >
>> > > >
>> > > >I hope that my explanation is a little clearer this time,
>> > > >
>> > > >Kevin
>> > > >
>> > > >
>> > > >
>> > > >"Gord Dibben" wrote:
>> > > >
>> > > >> Kevin
>> > > >>
>> > > >> Still not sure what your needs are. Copy cells or copy formats?
>> > > >>
>> > > >> Also "a number of them are grouped into 20 consecutive cells".
>> > > >>
>> > > >> How many and what is the criterion for deciding which 20 to copy?
>> > > >>
>> > > >> In blocks of 20 cells to where?
>> > > >>
>> > > >> This macro will copy cells only to another worksheet.
>> > > >>
>> > > >> Sub move20()
>> > > >> ActiveCell.Resize(20, 1).Copy Destination:=Worksheets("Sheet2").Range("A1")
>> > > >> End Sub
>> > > >>
>> > > >> Probably not what you want, but a start.
>> > > >>
>> > > >> No need for a new thread unless you want to post over in the excel.programming
>> > > >> group, but everyone over there reads this group also.
>> > > >>
>> > > >>
>> > > >> Gord
>> > > >>
>> > > >>
>> > > >> On Mon, 9 May 2005 15:10:07 -0700, kevinm <[email protected]>
>> > > >> wrote:
>> > > >>
>> > > >> >Gord/Myrna,
>> > > >> >
>> > > >> >thanks for your help, I went back and recreated the macro, I don't know what
>> > > >> >I did different but it is working now.
>> > > >> >
>> > > >> >All I need to figure out now is how to repeat the macro operation a number
>> > > >> >of times.
>> > > >> >
>> > > >> >Consider:
>> > > >> >
>> > > >> >My master worksheet has about 600 cells, all in one column. Of these a
>> > > >> >number of them are grouped into 20 consecutive cells. Instead of executing
>> > > >> >the macro twenty times to copy the 20 cells to worksheet 2, is there a way of
>> > > >> >looping the macro a user specified number of times?
>> > > >> >
>> > > >> >Let me know if I should start a new thread for this,
>> > > >> >
>> > > >> >thanks,
>> > > >> >
>> > > >> >Kevin
>> > > >> >
>> > > >> >
>> > > >> >"Gord Dibben" wrote:
>> > > >> >
>> > > >> >> 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 <[email protected]>
>> > > >> >> 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 Tools>Macro>Record 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 <[email protected]>
>> > > >> >> >> > 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

kevinm · 05-11-2005, 08:06 PM

Gord,

no worries, a few days isn't going to cause me any problem. Besides, when we
have this working it will probably save me days of work and pain anyway,

Kevin

"Gord Dibben" wrote:

> Kevin
>
> You've not been forgotten.
>
> Been busy but will try to get you something in the next day or so.
>
> Monday I'm starting a Consulting contract and will be out of town for most of
> every week until end of September so I better get you something before then.
>
> Gord
>
> On Wed, 11 May 2005 13:06:11 -0700, kevinm <[email protected]>
> wrote:
>
> >Hi Paul,
> >
> >thanks for the suggestion, I am certainly willing to give is a try but how
> >do I create this function, is it just another macro? Do I have to use the VB
> >editor to type in the function by hand?
> >
> >sorry for the stupid questions, I have not been to this depth with Excel
> >before,
> >
> >Kevin
> >
> >
> >"paul" wrote:
> >
> >> How about this for an idea.Have a custom worksheet function called say colour
> >> where a a cell (or selected range?) is shaded in relation to a certain
> >> value.I had a go using this code but it didnt like it
> >> Function colour()
> >> colour = Cells.Interior.
> >> .ColorIndex = 1
> >> .Pattern = xlSolid
> >> End Function
> >> =colour(1) would result in the selected cell or cells being shaded in black
> >> the values on sheet 1 could then be linked to the cells on sheet two to give
> >> the result kevin wants.I think perhaps it could be a function that would be
> >> more flexible than conditional formatting.
> >>
> >>
> >> --
> >> paul
> >> remove nospam for email addy!
> >>
> >>
> >>
> >> "kevinm" wrote:
> >>
> >> > Gord,
> >> >
> >> > I am glad you understand my requirements now. It sounds so simple to do when
> >> > I explain it in simple terms but I haven't been able to figure it out.
> >> >
> >> > If it is not possible I will just have to create a huge VBA file with a
> >> > separate procedure defined for every cell in column A, this is going to be
> >> > extremely tedious and error prone. In the real spreadsheet column A has about
> >> > 600 rows to process!
> >> >
> >> > If you (or anyone else reading watching this forum) can figure it out it
> >> > will save me an awful lot of time. Fingers crossed ..
> >> >
> >> > Kevin
> >> >
> >> >
> >> > "Gord Dibben" wrote:
> >> >
> >> > > Kevin
> >> > >
> >> > > I'd have to look at this one for a while. VBA is not my strong suit.
> >> > >
> >> > > Stick around here and wait for someone(there are many) with greater skills
> >> > > than myself.
> >> > >
> >> > > Meantime, I'll try to work on it between Tee-Times and other projects.
> >> > >
> >> > >
> >> > > Gord
> >> > >
> >> > > On Tue, 10 May 2005 09:01:08 -0700, kevinm <[email protected]>
> >> > > wrote:
> >> > >
> >> > > >Hi Gord,
> >> > > >
> >> > > >sorry I am doing a poor job explaining my requirements, it is difficult
> >> > > >without actually sending you an example spreadsheet. Let me have another go
> >> > > >at explaining, consider this simplified example:
> >> > > >
> >> > > >
> >> > > >
> >> > > >Sheet1 looks something like:
> >> > > >
> >> > > > Column
> >> > > >
> >> > > > A B C D E F
> >> > > >Row 1 C3 test Red_Cell
> >> > > >Row 2 D2 test Blue_Cell
> >> > > >Row 3 A4 test Green_Cell
> >> > > >Row 4 B3 test Pink_Cell
> >> > > >
> >> > > >
> >> > > >
> >> > > >
> >> > > >Initially Sheet2 looks like:
> >> > > >
> >> > > >
> >> > > > Column
> >> > > >
> >> > > > A B C D E F
> >> > > >Row 1
> >> > > >Row 2
> >> > > >Row 3
> >> > > >Row 4
> >> > > >
> >> > > >
> >> > > >
> >> > > >I would like a macro which work something like:
> >> > > >
> >> > > >Step1. Read cell value for first item in Sheet1 column A (A1 value = "C3")
> >> > > >Step2. Check the color format of the cell two columns to the right
> >> > > >(colored RED)
> >> > > >Step3. Go to Sheet2, color the cell whose reference was determined in
> >> > > >Step1 (C3) the color identified in Step2.
> >> > > >Step4. Read the cell value for the second item in Sheet1 column A (A2
> >> > > >value = "D2")
> >> > > >Step5. Check the color format of the cell two columns to the right
> >> > > >(colored BLUE)
> >> > > >Step6. Go to Sheet2, color the cell whose reference was determined in
> >> > > >Step4 (D2) the color identified in Step5.
> >> > > >Step7. Read the cell value for the third item in Sheet1 column A (A2 value
> >> > > >= "A4")
> >> > > >etc,
> >> > > >etc
> >> > > >
> >> > > >
> >> > > >When the macro has finished Sheet2 should look like:
> >> > > >
> >> > > >
> >> > > > Column
> >> > > >
> >> > > > A B C D E F
> >> > > >Row 1
> >> > > >Row 2 (blue)
> >> > > >Row 3 (pink) (red)
> >> > > >Row 4 (green)
> >> > > >
> >> > > >Where (xxx) is the color fill of the cell, not the value for the cell
> >> > > >contents.
> >> > > >
> >> > > >
> >> > > >I hope that my explanation is a little clearer this time,
> >> > > >
> >> > > >Kevin
> >> > > >
> >> > > >
> >> > > >
> >> > > >"Gord Dibben" wrote:
> >> > > >
> >> > > >> Kevin
> >> > > >>
> >> > > >> Still not sure what your needs are. Copy cells or copy formats?
> >> > > >>
> >> > > >> Also "a number of them are grouped into 20 consecutive cells".
> >> > > >>
> >> > > >> How many and what is the criterion for deciding which 20 to copy?
> >> > > >>
> >> > > >> In blocks of 20 cells to where?
> >> > > >>
> >> > > >> This macro will copy cells only to another worksheet.
> >> > > >>
> >> > > >> Sub move20()
> >> > > >> ActiveCell.Resize(20, 1).Copy Destination:=Worksheets("Sheet2").Range("A1")
> >> > > >> End Sub
> >> > > >>
> >> > > >> Probably not what you want, but a start.
> >> > > >>
> >> > > >> No need for a new thread unless you want to post over in the excel.programming
> >> > > >> group, but everyone over there reads this group also.
> >> > > >>
> >> > > >>
> >> > > >> Gord
> >> > > >>
> >> > > >>
> >> > > >> On Mon, 9 May 2005 15:10:07 -0700, kevinm <[email protected]>
> >> > > >> wrote:
> >> > > >>
> >> > > >> >Gord/Myrna,
> >> > > >> >
> >> > > >> >thanks for your help, I went back and recreated the macro, I don't know what
> >> > > >> >I did different but it is working now.
> >> > > >> >
> >> > > >> >All I need to figure out now is how to repeat the macro operation a number
> >> > > >> >of times.
> >> > > >> >
> >> > > >> >Consider:
> >> > > >> >
> >> > > >> >My master worksheet has about 600 cells, all in one column. Of these a
> >> > > >> >number of them are grouped into 20 consecutive cells. Instead of executing
> >> > > >> >the macro twenty times to copy the 20 cells to worksheet 2, is there a way of
> >> > > >> >looping the macro a user specified number of times?
> >> > > >> >
> >> > > >> >Let me know if I should start a new thread for this,
> >> > > >> >
> >> > > >> >thanks,
> >> > > >> >
> >> > > >> >Kevin
> >> > > >> >
> >> > > >> >
> >> > > >> >"Gord Dibben" wrote:
> >> > > >> >
> >> > > >> >> 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 <[email protected]>
> >> > > >> >> 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 Tools>Macro>Record New Macro and OK, the Stop Recording Toolbar
> >> > > >> >> >> > should pop up.
> >> > > >> >> >> >

paul · 05-17-2005, 07:06 AM

Kevin/Gordon this
http://www.microsoft.com/office/comm...1-2c0acdd69cbc

thread lead me to this
http://www.mvps.org/dmcritchie/excel/colors.htm
page,and the macro below it looks like this could be what kevin is after???

Setting Interior Color based on another Cell (#popbased)
Option Explicit
Global gblColorIndex As Integer
Sub SetInteriorColor()
gblColorIndex = ActiveCell.Interior.ColorIndex
End Sub

Sub PutInteriorColor()
Selection.Interior.ColorIndex = gblColorIndex
End Sub

Sub SameInteriorAsA1()
Selection.Interior.ColorIndex = [A1].Interior.ColorIndex
End Sub

--
paul
remove nospam for email addy!

"kevinm" wrote:

> Gord,
>
> no worries, a few days isn't going to cause me any problem. Besides, when we
> have this working it will probably save me days of work and pain anyway,
>
> Kevin
>
>
> "Gord Dibben" wrote:
>
> > Kevin
> >
> > You've not been forgotten.
> >
> > Been busy but will try to get you something in the next day or so.
> >
> > Monday I'm starting a Consulting contract and will be out of town for most of
> > every week until end of September so I better get you something before then.
> >
> > Gord
> >
> > On Wed, 11 May 2005 13:06:11 -0700, kevinm <[email protected]>
> > wrote:
> >
> > >Hi Paul,
> > >
> > >thanks for the suggestion, I am certainly willing to give is a try but how
> > >do I create this function, is it just another macro? Do I have to use the VB
> > >editor to type in the function by hand?
> > >
> > >sorry for the stupid questions, I have not been to this depth with Excel
> > >before,
> > >
> > >Kevin
> > >
> > >
> > >"paul" wrote:
> > >
> > >> How about this for an idea.Have a custom worksheet function called say colour
> > >> where a a cell (or selected range?) is shaded in relation to a certain
> > >> value.I had a go using this code but it didnt like it
> > >> Function colour()
> > >> colour = Cells.Interior.
> > >> .ColorIndex = 1
> > >> .Pattern = xlSolid
> > >> End Function
> > >> =colour(1) would result in the selected cell or cells being shaded in black
> > >> the values on sheet 1 could then be linked to the cells on sheet two to give
> > >> the result kevin wants.I think perhaps it could be a function that would be
> > >> more flexible than conditional formatting.
> > >>
> > >>
> > >> --
> > >> paul
> > >> remove nospam for email addy!
> > >>
> > >>
> > >>
> > >> "kevinm" wrote:
> > >>
> > >> > Gord,
> > >> >
> > >> > I am glad you understand my requirements now. It sounds so simple to do when
> > >> > I explain it in simple terms but I haven't been able to figure it out.
> > >> >
> > >> > If it is not possible I will just have to create a huge VBA file with a
> > >> > separate procedure defined for every cell in column A, this is going to be
> > >> > extremely tedious and error prone. In the real spreadsheet column A has about
> > >> > 600 rows to process!
> > >> >
> > >> > If you (or anyone else reading watching this forum) can figure it out it
> > >> > will save me an awful lot of time. Fingers crossed ..
> > >> >
> > >> > Kevin
> > >> >
> > >> >
> > >> > "Gord Dibben" wrote:
> > >> >
> > >> > > Kevin
> > >> > >
> > >> > > I'd have to look at this one for a while. VBA is not my strong suit.
> > >> > >
> > >> > > Stick around here and wait for someone(there are many) with greater skills
> > >> > > than myself.
> > >> > >
> > >> > > Meantime, I'll try to work on it between Tee-Times and other projects.
> > >> > >
> > >> > >
> > >> > > Gord
> > >> > >
> > >> > > On Tue, 10 May 2005 09:01:08 -0700, kevinm <[email protected]>
> > >> > > wrote:
> > >> > >
> > >> > > >Hi Gord,
> > >> > > >
> > >> > > >sorry I am doing a poor job explaining my requirements, it is difficult
> > >> > > >without actually sending you an example spreadsheet. Let me have another go
> > >> > > >at explaining, consider this simplified example:
> > >> > > >
> > >> > > >
> > >> > > >
> > >> > > >Sheet1 looks something like:
> > >> > > >
> > >> > > > Column
> > >> > > >
> > >> > > > A B C D E F
> > >> > > >Row 1 C3 test Red_Cell
> > >> > > >Row 2 D2 test Blue_Cell
> > >> > > >Row 3 A4 test Green_Cell
> > >> > > >Row 4 B3 test Pink_Cell
> > >> > > >
> > >> > > >
> > >> > > >
> > >> > > >
> > >> > > >Initially Sheet2 looks like:
> > >> > > >
> > >> > > >
> > >> > > > Column
> > >> > > >
> > >> > > > A B C D E F
> > >> > > >Row 1
> > >> > > >Row 2
> > >> > > >Row 3
> > >> > > >Row 4
> > >> > > >
> > >> > > >
> > >> > > >
> > >> > > >I would like a macro which work something like:
> > >> > > >
> > >> > > >Step1. Read cell value for first item in Sheet1 column A (A1 value = "C3")
> > >> > > >Step2. Check the color format of the cell two columns to the right
> > >> > > >(colored RED)
> > >> > > >Step3. Go to Sheet2, color the cell whose reference was determined in
> > >> > > >Step1 (C3) the color identified in Step2.
> > >> > > >Step4. Read the cell value for the second item in Sheet1 column A (A2
> > >> > > >value = "D2")
> > >> > > >Step5. Check the color format of the cell two columns to the right
> > >> > > >(colored BLUE)
> > >> > > >Step6. Go to Sheet2, color the cell whose reference was determined in
> > >> > > >Step4 (D2) the color identified in Step5.
> > >> > > >Step7. Read the cell value for the third item in Sheet1 column A (A2 value
> > >> > > >= "A4")
> > >> > > >etc,
> > >> > > >etc
> > >> > > >
> > >> > > >
> > >> > > >When the macro has finished Sheet2 should look like:
> > >> > > >
> > >> > > >
> > >> > > > Column
> > >> > > >
> > >> > > > A B C D E F
> > >> > > >Row 1
> > >> > > >Row 2 (blue)
> > >> > > >Row 3 (pink) (red)
> > >> > > >Row 4 (green)
> > >> > > >
> > >> > > >Where (xxx) is the color fill of the cell, not the value for the cell
> > >> > > >contents.
> > >> > > >
> > >> > > >
> > >> > > >I hope that my explanation is a little clearer this time,
> > >> > > >
> > >> > > >Kevin
> > >> > > >
> > >> > > >
> > >> > > >
> > >> > > >"Gord Dibben" wrote:
> > >> > > >
> > >> > > >> Kevin
> > >> > > >>
> > >> > > >> Still not sure what your needs are. Copy cells or copy formats?
> > >> > > >>
> > >> > > >> Also "a number of them are grouped into 20 consecutive cells".
> > >> > > >>
> > >> > > >> How many and what is the criterion for deciding which 20 to copy?
> > >> > > >>
> > >> > > >> In blocks of 20 cells to where?
> > >> > > >>
> > >> > > >> This macro will copy cells only to another worksheet.
> > >> > > >>
> > >> > > >> Sub move20()
> > >> > > >> ActiveCell.Resize(20, 1).Copy Destination:=Worksheets("Sheet2").Range("A1")
> > >> > > >> End Sub
> > >> > > >>
> > >> > > >> Probably not what you want, but a start.
> > >> > > >>
> > >> > > >> No need for a new thread unless you want to post over in the excel.programming
> > >> > > >> group, but everyone over there reads this group also.
> > >> > > >>
> > >> > > >>
> > >> > > >> Gord
> > >> > > >>
> > >> > > >>
> > >> > > >> On Mon, 9 May 2005 15:10:07 -0700, kevinm <[email protected]>
> > >> > > >> wrote:
> > >> > > >>
> > >> > > >> >Gord/Myrna,
> > >> > > >> >
> > >> > > >> >thanks for your help, I went back and recreated the macro, I don't know what
> > >> > > >> >I did different but it is working now.
> > >> > > >> >
> > >> > > >> >All I need to figure out now is how to repeat the macro operation a number
> > >> > > >> >of times.
> > >> > > >> >
> > >> > > >> >Consider:
> > >> > > >> >
> > >> > > >> >My master worksheet has about 600 cells, all in one column. Of these a
> > >> > > >> >number of them are grouped into 20 consecutive cells. Instead of executing
> > >> > > >> >the macro twenty times to copy the 20 cells to worksheet 2, is there a way of
> > >> > > >> >looping the macro a user specified number of times?
> > >> > > >> >
> > >> > > >> >Let me know if I should start a new thread for this,
> > >> > > >> >
> > >> > > >> >thanks,
> > >> > > >> >
> > >> > > >> >Kevin
> > >> > > >> >
> > >> > > >> >
> > >> > > >> >"Gord Dibben" wrote:
> > >> > > >> >
> > >> > > >> >> 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 <[email protected]>
> > >> > > >> >> 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,
> > >> > > >> >> >>

kevinm · 05-17-2005, 02:06 PM

Paul,

thanks for the pointer. Although I don't fully understand the VB code, if
the comments in the example code are anything to go by this would perform the
color copying from the cell on sheet1 to a different cell on sheet2.

The part I am still missing is how to read the cell contents on sheet1 and
use that to determine the target cell location on sheet2. I will quote the
simplified example from my earlier post ..

> >I would like a macro which work something like:

Step1. Read cell value for first item in Sheet1 column A (A1 value = "C3")
Step2. Check the color format of the cell two columns to the right
(colored RED)
Step3. Go to Sheet2, color the cell whose reference was determined in
Step1 (C3) the color identified in Step2.
Step4. Read the cell value for the second item in Sheet1 column A (A2
value = "D2")
Step5. Check the color format of the cell two columns to the right
(colored BLUE)
Step6. Go to Sheet2, color the cell whose reference was determined in
Step4 (D2) the color identified in Step5.
Step7. Read the cell value for the third item in Sheet1 column A (A2 value
= "A4")
etc,
etc

regards,

Kevin

"paul" wrote:

> Kevin/Gordon this
> http://www.microsoft.com/office/comm...1-2c0acdd69cbc
>
> thread lead me to this
> http://www.mvps.org/dmcritchie/excel/colors.htm
> page,and the macro below it looks like this could be what kevin is after???
>
> Setting Interior Color based on another Cell (#popbased)
> Option Explicit
> Global gblColorIndex As Integer
> Sub SetInteriorColor()
> gblColorIndex = ActiveCell.Interior.ColorIndex
> End Sub
>
> Sub PutInteriorColor()
> Selection.Interior.ColorIndex = gblColorIndex
> End Sub
>
> Sub SameInteriorAsA1()
> Selection.Interior.ColorIndex = [A1].Interior.ColorIndex
> End Sub
>
> --
> paul
> remove nospam for email addy!
>
>
>
> "kevinm" wrote:
>
> > Gord,
> >
> > no worries, a few days isn't going to cause me any problem. Besides, when we
> > have this working it will probably save me days of work and pain anyway,
> >
> > Kevin
> >
> >
> > "Gord Dibben" wrote:
> >
> > > Kevin
> > >
> > > You've not been forgotten.
> > >
> > > Been busy but will try to get you something in the next day or so.
> > >
> > > Monday I'm starting a Consulting contract and will be out of town for most of
> > > every week until end of September so I better get you something before then.
> > >
> > > Gord
> > >
> > > On Wed, 11 May 2005 13:06:11 -0700, kevinm <[email protected]>
> > > wrote:
> > >
> > > >Hi Paul,
> > > >
> > > >thanks for the suggestion, I am certainly willing to give is a try but how
> > > >do I create this function, is it just another macro? Do I have to use the VB
> > > >editor to type in the function by hand?
> > > >
> > > >sorry for the stupid questions, I have not been to this depth with Excel
> > > >before,
> > > >
> > > >Kevin
> > > >
> > > >
> > > >"paul" wrote:
> > > >
> > > >> How about this for an idea.Have a custom worksheet function called say colour
> > > >> where a a cell (or selected range?) is shaded in relation to a certain
> > > >> value.I had a go using this code but it didnt like it
> > > >> Function colour()
> > > >> colour = Cells.Interior.
> > > >> .ColorIndex = 1
> > > >> .Pattern = xlSolid
> > > >> End Function
> > > >> =colour(1) would result in the selected cell or cells being shaded in black
> > > >> the values on sheet 1 could then be linked to the cells on sheet two to give
> > > >> the result kevin wants.I think perhaps it could be a function that would be
> > > >> more flexible than conditional formatting.
> > > >>
> > > >>
> > > >> --
> > > >> paul
> > > >> remove nospam for email addy!
> > > >>
> > > >>
> > > >>
> > > >> "kevinm" wrote:
> > > >>
> > > >> > Gord,
> > > >> >
> > > >> > I am glad you understand my requirements now. It sounds so simple to do when
> > > >> > I explain it in simple terms but I haven't been able to figure it out.
> > > >> >
> > > >> > If it is not possible I will just have to create a huge VBA file with a
> > > >> > separate procedure defined for every cell in column A, this is going to be
> > > >> > extremely tedious and error prone. In the real spreadsheet column A has about
> > > >> > 600 rows to process!
> > > >> >
> > > >> > If you (or anyone else reading watching this forum) can figure it out it
> > > >> > will save me an awful lot of time. Fingers crossed ..
> > > >> >
> > > >> > Kevin
> > > >> >
> > > >> >
> > > >> > "Gord Dibben" wrote:
> > > >> >
> > > >> > > Kevin
> > > >> > >
> > > >> > > I'd have to look at this one for a while. VBA is not my strong suit.
> > > >> > >
> > > >> > > Stick around here and wait for someone(there are many) with greater skills
> > > >> > > than myself.
> > > >> > >
> > > >> > > Meantime, I'll try to work on it between Tee-Times and other projects.
> > > >> > >
> > > >> > >
> > > >> > > Gord
> > > >> > >
> > > >> > > On Tue, 10 May 2005 09:01:08 -0700, kevinm <[email protected]>
> > > >> > > wrote:
> > > >> > >
> > > >> > > >Hi Gord,
> > > >> > > >
> > > >> > > >sorry I am doing a poor job explaining my requirements, it is difficult
> > > >> > > >without actually sending you an example spreadsheet. Let me have another go
> > > >> > > >at explaining, consider this simplified example:
> > > >> > > >
> > > >> > > >
> > > >> > > >
> > > >> > > >Sheet1 looks something like:
> > > >> > > >
> > > >> > > > Column
> > > >> > > >
> > > >> > > > A B C D E F
> > > >> > > >Row 1 C3 test Red_Cell
> > > >> > > >Row 2 D2 test Blue_Cell
> > > >> > > >Row 3 A4 test Green_Cell
> > > >> > > >Row 4 B3 test Pink_Cell
> > > >> > > >
> > > >> > > >
> > > >> > > >
> > > >> > > >
> > > >> > > >Initially Sheet2 looks like:
> > > >> > > >
> > > >> > > >
> > > >> > > > Column
> > > >> > > >
> > > >> > > > A B C D E F
> > > >> > > >Row 1
> > > >> > > >Row 2
> > > >> > > >Row 3
> > > >> > > >Row 4
> > > >> > > >
> > > >> > > >
> > > >> > > >
> > > >> > > >I would like a macro which work something like:
> > > >> > > >
> > > >> > > >Step1. Read cell value for first item in Sheet1 column A (A1 value = "C3")
> > > >> > > >Step2. Check the color format of the cell two columns to the right
> > > >> > > >(colored RED)
> > > >> > > >Step3. Go to Sheet2, color the cell whose reference was determined in
> > > >> > > >Step1 (C3) the color identified in Step2.
> > > >> > > >Step4. Read the cell value for the second item in Sheet1 column A (A2
> > > >> > > >value = "D2")
> > > >> > > >Step5. Check the color format of the cell two columns to the right
> > > >> > > >(colored BLUE)
> > > >> > > >Step6. Go to Sheet2, color the cell whose reference was determined in
> > > >> > > >Step4 (D2) the color identified in Step5.
> > > >> > > >Step7. Read the cell value for the third item in Sheet1 column A (A2 value
> > > >> > > >= "A4")
> > > >> > > >etc,
> > > >> > > >etc
> > > >> > > >
> > > >> > > >
> > > >> > > >When the macro has finished Sheet2 should look like:
> > > >> > > >
> > > >> > > >
> > > >> > > > Column
> > > >> > > >
> > > >> > > > A B C D E F
> > > >> > > >Row 1
> > > >> > > >Row 2 (blue)
> > > >> > > >Row 3 (pink) (red)
> > > >> > > >Row 4 (green)
> > > >> > > >
> > > >> > > >Where (xxx) is the color fill of the cell, not the value for the cell
> > > >> > > >contents.
> > > >> > > >
> > > >> > > >
> > > >> > > >I hope that my explanation is a little clearer this time,
> > > >> > > >
> > > >> > > >Kevin
> > > >> > > >
> > > >> > > >
> > > >> > > >
> > > >> > > >"Gord Dibben" wrote:
> > > >> > > >
> > > >> > > >> Kevin
> > > >> > > >>
> > > >> > > >> Still not sure what your needs are. Copy cells or copy formats?
> > > >> > > >>
> > > >> > > >> Also "a number of them are grouped into 20 consecutive cells".
> > > >> > > >>
> > > >> > > >> How many and what is the criterion for deciding which 20 to copy?
> > > >> > > >>
> > > >> > > >> In blocks of 20 cells to where?
> > > >> > > >>
> > > >> > > >> This macro will copy cells only to another worksheet.
> > > >> > > >>
> > > >> > > >> Sub move20()
> > > >> > > >> ActiveCell.Resize(20, 1).Copy Destination:=Worksheets("Sheet2").Range("A1")
> > > >> > > >> End Sub
> > > >> > > >>
> > > >> > > >> Probably not what you want, but a start.
> > > >> > > >>
> > > >> > > >> No need for a new thread unless you want to post over in the excel.programming
> > > >> > > >> group, but everyone over there reads this group also.
> > > >> > > >>
> > > >> > > >>
> > > >> > > >> Gord
> > > >> > > >>
> > > >> > > >>
> > > >> > > >> On Mon, 9 May 2005 15:10:07 -0700, kevinm <[email protected]>
> > > >> > > >> wrote:
> > > >> > > >>
> > > >> > > >> >Gord/Myrna,
> > > >> > > >> >
> > > >> > > >> >thanks for your help, I went back and recreated the macro, I don't know what
> > > >> > > >> >I did different but it is working now.
> > > >> > > >> >
> > > >> > > >> >All I need to figure out now is how to repeat the macro operation a number
> > > >> > > >> >of times.
> > > >> > > >> >
> > > >> > > >> >Consider:
> > > >> > > >> >
> > > >> > > >> >My master worksheet has about 600 cells, all in one column. Of these a
> > > >> > > >> >number of them are grouped into 20 consecutive cells. Instead of executing
> > > >> > > >> >the macro twenty times to copy the 20 cells to worksheet 2, is there a way of
> > > >> > > >> >looping the macro a user specified number of times?
> > > >> > > >> >
> > > >> > > >> >Let me know if I should start a new thread for this,
> > > >> > > >> >
> > > >> > > >> >thanks,
> > > >> > > >> >
> > > >> > > >> >Kevin
> > > >> > > >> >
> > > >> > > >> >
> > > >> > > >> >"Gord Dibben" wrote:
> > > >> > > >> >
> > > >> > > >> >> 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 <[email protected]>
> > > >> > > >> >> 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

paul · 05-19-2005, 07:06 AM

kevin I have a simple macro which does what you want.I am not quite sure
where to go from here tho.I can email it to you and maybe you can ask on the
programming forum how you can modify it to "loop" through your ranges or pin
blocks The only catch is that instead of colours they are numbers..you will
see what i mean ie black is colorindex = 1,whte is colorindex = 2,etc etc
--
paul
remove nospam for email addy!

"kevinm" wrote:

> Paul,
>
> thanks for the pointer. Although I don't fully understand the VB code, if
> the comments in the example code are anything to go by this would perform the
> color copying from the cell on sheet1 to a different cell on sheet2.
>
> The part I am still missing is how to read the cell contents on sheet1 and
> use that to determine the target cell location on sheet2. I will quote the
> simplified example from my earlier post ..
>
>
> > >I would like a macro which work something like:
>
> Step1. Read cell value for first item in Sheet1 column A (A1 value = "C3")
> Step2. Check the color format of the cell two columns to the right
> (colored RED)
> Step3. Go to Sheet2, color the cell whose reference was determined in
> Step1 (C3) the color identified in Step2.
> Step4. Read the cell value for the second item in Sheet1 column A (A2
> value = "D2")
> Step5. Check the color format of the cell two columns to the right
> (colored BLUE)
> Step6. Go to Sheet2, color the cell whose reference was determined in
> Step4 (D2) the color identified in Step5.
> Step7. Read the cell value for the third item in Sheet1 column A (A2 value
> = "A4")
> etc,
> etc
>
> regards,
>
> Kevin
>
>
> "paul" wrote:
>
> > Kevin/Gordon this
> > http://www.microsoft.com/office/comm...1-2c0acdd69cbc
> >
> > thread lead me to this
> > http://www.mvps.org/dmcritchie/excel/colors.htm
> > page,and the macro below it looks like this could be what kevin is after???
> >
> > Setting Interior Color based on another Cell (#popbased)
> > Option Explicit
> > Global gblColorIndex As Integer
> > Sub SetInteriorColor()
> > gblColorIndex = ActiveCell.Interior.ColorIndex
> > End Sub
> >
> > Sub PutInteriorColor()
> > Selection.Interior.ColorIndex = gblColorIndex
> > End Sub
> >
> > Sub SameInteriorAsA1()
> > Selection.Interior.ColorIndex = [A1].Interior.ColorIndex
> > End Sub
> >
> > --
> > paul
> > remove nospam for email addy!
> >
> >
> >
> > "kevinm" wrote:
> >
> > > Gord,
> > >
> > > no worries, a few days isn't going to cause me any problem. Besides, when we
> > > have this working it will probably save me days of work and pain anyway,
> > >
> > > Kevin
> > >
> > >
> > > "Gord Dibben" wrote:
> > >
> > > > Kevin
> > > >
> > > > You've not been forgotten.
> > > >
> > > > Been busy but will try to get you something in the next day or so.
> > > >
> > > > Monday I'm starting a Consulting contract and will be out of town for most of
> > > > every week until end of September so I better get you something before then.
> > > >
> > > > Gord
> > > >
> > > > On Wed, 11 May 2005 13:06:11 -0700, kevinm <[email protected]>
> > > > wrote:
> > > >
> > > > >Hi Paul,
> > > > >
> > > > >thanks for the suggestion, I am certainly willing to give is a try but how
> > > > >do I create this function, is it just another macro? Do I have to use the VB
> > > > >editor to type in the function by hand?
> > > > >
> > > > >sorry for the stupid questions, I have not been to this depth with Excel
> > > > >before,
> > > > >
> > > > >Kevin
> > > > >
> > > > >
> > > > >"paul" wrote:
> > > > >
> > > > >> How about this for an idea.Have a custom worksheet function called say colour
> > > > >> where a a cell (or selected range?) is shaded in relation to a certain
> > > > >> value.I had a go using this code but it didnt like it
> > > > >> Function colour()
> > > > >> colour = Cells.Interior.
> > > > >> .ColorIndex = 1
> > > > >> .Pattern = xlSolid
> > > > >> End Function
> > > > >> =colour(1) would result in the selected cell or cells being shaded in black
> > > > >> the values on sheet 1 could then be linked to the cells on sheet two to give
> > > > >> the result kevin wants.I think perhaps it could be a function that would be
> > > > >> more flexible than conditional formatting.
> > > > >>
> > > > >>
> > > > >> --
> > > > >> paul
> > > > >> remove nospam for email addy!
> > > > >>
> > > > >>
> > > > >>
> > > > >> "kevinm" wrote:
> > > > >>
> > > > >> > Gord,
> > > > >> >
> > > > >> > I am glad you understand my requirements now. It sounds so simple to do when
> > > > >> > I explain it in simple terms but I haven't been able to figure it out.
> > > > >> >
> > > > >> > If it is not possible I will just have to create a huge VBA file with a
> > > > >> > separate procedure defined for every cell in column A, this is going to be
> > > > >> > extremely tedious and error prone. In the real spreadsheet column A has about
> > > > >> > 600 rows to process!
> > > > >> >
> > > > >> > If you (or anyone else reading watching this forum) can figure it out it
> > > > >> > will save me an awful lot of time. Fingers crossed ..
> > > > >> >
> > > > >> > Kevin
> > > > >> >
> > > > >> >
> > > > >> > "Gord Dibben" wrote:
> > > > >> >
> > > > >> > > Kevin
> > > > >> > >
> > > > >> > > I'd have to look at this one for a while. VBA is not my strong suit.
> > > > >> > >
> > > > >> > > Stick around here and wait for someone(there are many) with greater skills
> > > > >> > > than myself.
> > > > >> > >
> > > > >> > > Meantime, I'll try to work on it between Tee-Times and other projects.
> > > > >> > >
> > > > >> > >
> > > > >> > > Gord
> > > > >> > >
> > > > >> > > On Tue, 10 May 2005 09:01:08 -0700, kevinm <[email protected]>
> > > > >> > > wrote:
> > > > >> > >
> > > > >> > > >Hi Gord,
> > > > >> > > >
> > > > >> > > >sorry I am doing a poor job explaining my requirements, it is difficult
> > > > >> > > >without actually sending you an example spreadsheet. Let me have another go
> > > > >> > > >at explaining, consider this simplified example:
> > > > >> > > >
> > > > >> > > >
> > > > >> > > >
> > > > >> > > >Sheet1 looks something like:
> > > > >> > > >
> > > > >> > > > Column
> > > > >> > > >
> > > > >> > > > A B C D E F
> > > > >> > > >Row 1 C3 test Red_Cell
> > > > >> > > >Row 2 D2 test Blue_Cell
> > > > >> > > >Row 3 A4 test Green_Cell
> > > > >> > > >Row 4 B3 test Pink_Cell
> > > > >> > > >
> > > > >> > > >
> > > > >> > > >
> > > > >> > > >
> > > > >> > > >Initially Sheet2 looks like:
> > > > >> > > >
> > > > >> > > >
> > > > >> > > > Column
> > > > >> > > >
> > > > >> > > > A B C D E F
> > > > >> > > >Row 1
> > > > >> > > >Row 2
> > > > >> > > >Row 3
> > > > >> > > >Row 4
> > > > >> > > >
> > > > >> > > >
> > > > >> > > >
> > > > >> > > >I would like a macro which work something like:
> > > > >> > > >
> > > > >> > > >Step1. Read cell value for first item in Sheet1 column A (A1 value = "C3")
> > > > >> > > >Step2. Check the color format of the cell two columns to the right
> > > > >> > > >(colored RED)
> > > > >> > > >Step3. Go to Sheet2, color the cell whose reference was determined in
> > > > >> > > >Step1 (C3) the color identified in Step2.
> > > > >> > > >Step4. Read the cell value for the second item in Sheet1 column A (A2
> > > > >> > > >value = "D2")
> > > > >> > > >Step5. Check the color format of the cell two columns to the right
> > > > >> > > >(colored BLUE)
> > > > >> > > >Step6. Go to Sheet2, color the cell whose reference was determined in
> > > > >> > > >Step4 (D2) the color identified in Step5.
> > > > >> > > >Step7. Read the cell value for the third item in Sheet1 column A (A2 value
> > > > >> > > >= "A4")
> > > > >> > > >etc,
> > > > >> > > >etc
> > > > >> > > >
> > > > >> > > >
> > > > >> > > >When the macro has finished Sheet2 should look like:
> > > > >> > > >
> > > > >> > > >
> > > > >> > > > Column
> > > > >> > > >
> > > > >> > > > A B C D E F
> > > > >> > > >Row 1
> > > > >> > > >Row 2 (blue)
> > > > >> > > >Row 3 (pink) (red)
> > > > >> > > >Row 4 (green)
> > > > >> > > >
> > > > >> > > >Where (xxx) is the color fill of the cell, not the value for the cell
> > > > >> > > >contents.
> > > > >> > > >
> > > > >> > > >
> > > > >> > > >I hope that my explanation is a little clearer this time,
> > > > >> > > >
> > > > >> > > >Kevin
> > > > >> > > >
> > > > >> > > >
> > > > >> > > >
> > > > >> > > >"Gord Dibben" wrote:
> > > > >> > > >
> > > > >> > > >> Kevin
> > > > >> > > >>
> > > > >> > > >> Still not sure what your needs are. Copy cells or copy formats?
> > > > >> > > >>
> > > > >> > > >> Also "a number of them are grouped into 20 consecutive cells".
> > > > >> > > >>
> > > > >> > > >> How many and what is the criterion for deciding which 20 to copy?
> > > > >> > > >>
> > > > >> > > >> In blocks of 20 cells to where?
> > > > >> > > >>
> > > > >> > > >> This macro will copy cells only to another worksheet.
> > > > >> > > >>
> > > > >> > > >> Sub move20()
> > > > >> > > >> ActiveCell.Resize(20, 1).Copy Destination:=Worksheets("Sheet2").Range("A1")
> > > > >> > > >> End Sub
> > > > >> > > >>
> > > > >> > > >> Probably not what you want, but a start.
> > > > >> > > >>
> > > > >> > > >> No need for a new thread unless you want to post over in the excel.programming
> > > > >> > > >> group, but everyone over there reads this group also.
> > > > >> > > >>
> > > > >> > > >>
> > > > >> > > >> Gord
> > > > >> > > >>
> > > > >> > > >>
> > > > >> > > >> On Mon, 9 May 2005 15:10:07 -0700, kevinm <[email protected]>
> > > > >> > > >> wrote:
> > > > >> > > >>
> > > > >> > > >> >Gord/Myrna,
> > > > >> > > >> >
> > > > >> > > >> >thanks for your help, I went back and recreated the macro, I don't know what
> > > > >> > > >> >I did different but it is working now.
> > > > >> > > >> >
> > > > >> > > >> >All I need to figure out now is how to repeat the macro operation a number
> > > > >> > > >> >of times.
> > > > >> > > >> >
> > > > >> > > >> >Consider:
> > > > >> > > >> >
> > > > >> > > >> >My master worksheet has about 600 cells, all in one column. Of these a
> > > > >> > > >> >number of them are grouped into 20 consecutive cells. Instead of executing
> > > > >> > > >> >the macro twenty times to copy the 20 cells to worksheet 2, is there a way of
> > > > >> > > >> >looping the macro a user specified number of times?
> > > > >> > > >> >
> > > > >> > > >> >Let me know if I should start a new thread for this,
> > > > >> > > >> >
> > > > >> > > >> >thanks,
> > > > >> > > >> >
> > > > >> > > >> >Kevin
> > > > >> > > >> >
> > > > >> > > >> >
> > > > >> > > >> >"Gord Dibben" wrote:
> > > > >> > > >> >
> > > > >> > > >> >> 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

Copy cell format to cell on another worksht and update automatical

LinkBack

Thread Tools

Rate This Thread

Display

Copy cell format to cell on another worksht and update automatical

RE: Copy cell format to cell on another worksht and update automatical

RE: Copy cell format to cell on another worksht and update automat

Re: Copy cell format to cell on another worksht and update automat

Re: Copy cell format to cell on another worksht and update automat

Re: Copy cell format to cell on another worksht and update automat

Re: Copy cell format to cell on another worksht and update automat

Re: Copy cell format to cell on another worksht and update automat

Re: Copy cell format to cell on another worksht and update automat

Re: Copy cell format to cell on another worksht and update automat

Re: Copy cell format to cell on another worksht and update automat

Re: Copy cell format to cell on another worksht and update automat

Re: Copy cell format to cell on another worksht and update automat

Re: Copy cell format to cell on another worksht and update automat

Re: Copy cell format to cell on another worksht and update automat

Re: Copy cell format to cell on another worksht and update automat

Re: Copy cell format to cell on another worksht and update automat

Re: Copy cell format to cell on another worksht and update automat

Re: Copy cell format to cell on another worksht and update automat

Re: Copy cell format to cell on another worksht and update automat

Re: Copy cell format to cell on another worksht and update automat

Re: Copy cell format to cell on another worksht and update automat

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions