Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
kevinm
 
Posts: n/a
Default Help with macro looping and color query function

I have been struggling to get a macro together to perform some tricky
coloring functions. With the kind help of a number of people in the 'Excel
Worksheet Forum' I have got close to achieving something which works but need
help to get the macro to run. Here is where I have got to:


The following is an example macro, kindly provided to me by Paul who has
been helping me in the worksheet forum:


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 19/05/2005
'

'
mycell = Range("A1").Value
mycolor = Range("C1").Value
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
Sheets("Sheet1").Select
mycell = Range("A2").Value
mycolor = Range("c2")
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
Sheets("Sheet1").Select
mycell = Range("A3").Value
mycolor = Range("C3")
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
End Sub



Here is a list of the modifications which I require to make to the above
macro:

1) Modify it so that instead of having the absolute cell references in the
code, I want it to work on a range of cells in column A (I have 600+ rows to
process, I don't want to have a macro that has a separate section for A1, A2,
A3 .. A600, etc as it would be unmanageable).

2) The macro currently checks a cell in column C and colors a cell on sheet
2, the sheet2 cell reference comes from checking the row A cell value.
However, what I really want to do is query the color of the column cell, and
use that same color for the target cell on sheet 2.


I have not found a way of doing 2) yet, but I have attempted to modify the
macro to do 1) but have failed to come up with anything which doesn't result
in an VB error. Could someone please help me with this?

(I can provide Paul's example spreadsheet/macro if it is helpful to you)


thanks,

Kevin

  #2   Report Post  
klafert
 
Posts: n/a
Default



"provided to me by Paul who has
been helping me in the worksheet forum:


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 19/05/2005
'

'
mycell = Range("A1").Value
mycolor = Range("C1").Value
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
Sheets("Sheet1").Select
mycell = Range("A2").Value
mycolor = Range("c2")
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
Sheets("Sheet1").Select
mycell = Range("A3").Value
mycolor = Range("C3")
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
End Sub



Here is a list of the modifications which I require to make to the above
macro:

1) Modify it so that instead of having the absolute cell references in the
code, I want it to work on a range of cells in column A (I have 600+ rows to
process, I don't want to have a macro that has a separate section for A1, A2,
A3 .. A600, etc as it would be unmanageable).

2) The macro currently checks a cell in column C and colors a cell on sheet
2, the sheet2 cell reference comes from checking the row A cell value.
However, what I really want to do is query the color of the column cell, and
use that same color for the target cell on sheet 2.


I have not found a way of doing 2) yet, but I have attempted to modify the
macro to do 1) but have failed to come up with anything which doesn't result
in an VB error. Could someone please help me with this?

(I can provide Paul's example spreadsheet/macro if it is helpful to you)


thanks,

Kevin

  #3   Report Post  
bj
 
Posts: n/a
Default

try something like be in sheet2
Sub try()
For r = 1 To 600
Cells(r, 1).Interior.ColorIndex = Sheets("Sheet1").Cells(r,
1).Interior.ColorIndex
Next r
End Sub

you may have to play with which sheet you are copying from

"klafert" wrote:



"provided to me by Paul who has
been helping me in the worksheet forum:


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 19/05/2005
'

'
mycell = Range("A1").Value
mycolor = Range("C1").Value
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
Sheets("Sheet1").Select
mycell = Range("A2").Value
mycolor = Range("c2")
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
Sheets("Sheet1").Select
mycell = Range("A3").Value
mycolor = Range("C3")
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
End Sub



Here is a list of the modifications which I require to make to the above
macro:

1) Modify it so that instead of having the absolute cell references in the
code, I want it to work on a range of cells in column A (I have 600+ rows to
process, I don't want to have a macro that has a separate section for A1, A2,
A3 .. A600, etc as it would be unmanageable).

2) The macro currently checks a cell in column C and colors a cell on sheet
2, the sheet2 cell reference comes from checking the row A cell value.
However, what I really want to do is query the color of the column cell, and
use that same color for the target cell on sheet 2.


I have not found a way of doing 2) yet, but I have attempted to modify the
macro to do 1) but have failed to come up with anything which doesn't result
in an VB error. Could someone please help me with this?

(I can provide Paul's example spreadsheet/macro if it is helpful to you)


thanks,

Kevin

  #4   Report Post  
kevinm
 
Posts: n/a
Default

bj,
thanks for your suggestion, sorry to be slow on the uptake but could you
help by explaining how your code fits with the original which I posted when I
started this thread,

Kevin


"bj" wrote:

try something like be in sheet2
Sub try()
For r = 1 To 600
Cells(r, 1).Interior.ColorIndex = Sheets("Sheet1").Cells(r,
1).Interior.ColorIndex
Next r
End Sub

you may have to play with which sheet you are copying from

"klafert" wrote:



"provided to me by Paul who has
been helping me in the worksheet forum:


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 19/05/2005
'

'
mycell = Range("A1").Value
mycolor = Range("C1").Value
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
Sheets("Sheet1").Select
mycell = Range("A2").Value
mycolor = Range("c2")
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
Sheets("Sheet1").Select
mycell = Range("A3").Value
mycolor = Range("C3")
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
End Sub



Here is a list of the modifications which I require to make to the above
macro:

1) Modify it so that instead of having the absolute cell references in the
code, I want it to work on a range of cells in column A (I have 600+ rows to
process, I don't want to have a macro that has a separate section for A1, A2,
A3 .. A600, etc as it would be unmanageable).

2) The macro currently checks a cell in column C and colors a cell on sheet
2, the sheet2 cell reference comes from checking the row A cell value.
However, what I really want to do is query the color of the column cell, and
use that same color for the target cell on sheet 2.


I have not found a way of doing 2) yet, but I have attempted to modify the
macro to do 1) but have failed to come up with anything which doesn't result
in an VB error. Could someone please help me with this?

(I can provide Paul's example spreadsheet/macro if it is helpful to you)


thanks,

Kevin

  #5   Report Post  
kevinm
 
Posts: n/a
Default

bj,

I think I am close to having something which might work but I cant figure
out what looks to be a simple syntax problem, my lack of knowledge of VB
letting me down again :-(

Anyway, here is what I have got ..

Sub Macro2()
'
'
For r = 1 To 3
mycolor = Sheets("Sheet1").Cells(r, 3).Interior.ColorIndex
mycell = Cells(r, 1).Value
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
Next r
End Sub

When I run this the correct cell on sheet2 gets colored correctly but it
never gets past this first loop and fails as follows ..

"Run-time error '1004':
Method 'Range' of object'_Global' failed"

Basically I tried to take what you had given me and use it with the stuff
that Paul had given me previously, seems like there I am using 'Range'
incorrectly somehow,

can you please help,

thanks,

Kevin


"kevinm" wrote:

bj,
thanks for your suggestion, sorry to be slow on the uptake but could you
help by explaining how your code fits with the original which I posted when I
started this thread,

Kevin


"bj" wrote:

try something like be in sheet2
Sub try()
For r = 1 To 600
Cells(r, 1).Interior.ColorIndex = Sheets("Sheet1").Cells(r,
1).Interior.ColorIndex
Next r
End Sub

you may have to play with which sheet you are copying from

"klafert" wrote:



"provided to me by Paul who has
been helping me in the worksheet forum:


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 19/05/2005
'

'
mycell = Range("A1").Value
mycolor = Range("C1").Value
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
Sheets("Sheet1").Select
mycell = Range("A2").Value
mycolor = Range("c2")
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
Sheets("Sheet1").Select
mycell = Range("A3").Value
mycolor = Range("C3")
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
End Sub



Here is a list of the modifications which I require to make to the above
macro:

1) Modify it so that instead of having the absolute cell references in the
code, I want it to work on a range of cells in column A (I have 600+ rows to
process, I don't want to have a macro that has a separate section for A1, A2,
A3 .. A600, etc as it would be unmanageable).

2) The macro currently checks a cell in column C and colors a cell on sheet
2, the sheet2 cell reference comes from checking the row A cell value.
However, what I really want to do is query the color of the column cell, and
use that same color for the target cell on sheet 2.


I have not found a way of doing 2) yet, but I have attempted to modify the
macro to do 1) but have failed to come up with anything which doesn't result
in an VB error. Could someone please help me with this?

(I can provide Paul's example spreadsheet/macro if it is helpful to you)


thanks,

Kevin



  #6   Report Post  
kevinm
 
Posts: n/a
Default

bj,

I forgot to say that when I run the debug mode, it highlights the following
line as having the error:

Range(mycell).Select


hope this helps,

Kevin


"kevinm" wrote:

bj,

I think I am close to having something which might work but I cant figure
out what looks to be a simple syntax problem, my lack of knowledge of VB
letting me down again :-(

Anyway, here is what I have got ..

Sub Macro2()
'
'
For r = 1 To 3
mycolor = Sheets("Sheet1").Cells(r, 3).Interior.ColorIndex
mycell = Cells(r, 1).Value
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
Next r
End Sub

When I run this the correct cell on sheet2 gets colored correctly but it
never gets past this first loop and fails as follows ..

"Run-time error '1004':
Method 'Range' of object'_Global' failed"

Basically I tried to take what you had given me and use it with the stuff
that Paul had given me previously, seems like there I am using 'Range'
incorrectly somehow,

can you please help,

thanks,

Kevin


"kevinm" wrote:

bj,
thanks for your suggestion, sorry to be slow on the uptake but could you
help by explaining how your code fits with the original which I posted when I
started this thread,

Kevin


"bj" wrote:

try something like be in sheet2
Sub try()
For r = 1 To 600
Cells(r, 1).Interior.ColorIndex = Sheets("Sheet1").Cells(r,
1).Interior.ColorIndex
Next r
End Sub

you may have to play with which sheet you are copying from

"klafert" wrote:



"provided to me by Paul who has
been helping me in the worksheet forum:


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 19/05/2005
'

'
mycell = Range("A1").Value
mycolor = Range("C1").Value
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
Sheets("Sheet1").Select
mycell = Range("A2").Value
mycolor = Range("c2")
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
Sheets("Sheet1").Select
mycell = Range("A3").Value
mycolor = Range("C3")
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
End Sub



Here is a list of the modifications which I require to make to the above
macro:

1) Modify it so that instead of having the absolute cell references in the
code, I want it to work on a range of cells in column A (I have 600+ rows to
process, I don't want to have a macro that has a separate section for A1, A2,
A3 .. A600, etc as it would be unmanageable).

2) The macro currently checks a cell in column C and colors a cell on sheet
2, the sheet2 cell reference comes from checking the row A cell value.
However, what I really want to do is query the color of the column cell, and
use that same color for the target cell on sheet 2.


I have not found a way of doing 2) yet, but I have attempted to modify the
macro to do 1) but have failed to come up with anything which doesn't result
in an VB error. Could someone please help me with this?

(I can provide Paul's example spreadsheet/macro if it is helpful to you)


thanks,

Kevin

  #7   Report Post  
bj
 
Posts: n/a
Default

If I read your initial post correct You want to match the cells in Column A
sheet 2 to have the same color as the cells in Column A Sheet 1
The macro probably should have been

Sub try()
'source column Sheet 1 column A (1)
'target column sheet2 column A (1)
For r = 1 To 600
'r represents rows
Sheets("Sheet2").Cells(r, 1).Interior.ColorIndex = Sheets("Sheet1").Cells(r,
1).Interior.ColorIndex
' Cells(1,1) would be the same as A1, Cells(10,2) would be the same
as B10
Next r
End Sub


Then you wouldn't have had to be in Sheet2 to run the macro. Reading your
posting again It may have been column C Sheet 1you wanted to match in this
case make the macro be


Sub try()
'source column Sheet 1 column C (3)
'target column sheet2 column A (1)
For r = 1 To 600
'r represents rows
Sheets("Sheet2").Cells(r, 1).Interior.ColorIndex = Sheets("Sheet1").Cells(r,
3).Interior.ColorIndex
' Cells(1,1) would be the same as A1, Cells(10,2) would be the same
as B10
Next r
End Sub

"kevinm" wrote:

bj,
thanks for your suggestion, sorry to be slow on the uptake but could you
help by explaining how your code fits with the original which I posted when I
started this thread,

Kevin


"bj" wrote:

try something like be in sheet2
Sub try()
For r = 1 To 600
Cells(r, 1).Interior.ColorIndex = Sheets("Sheet1").Cells(r,
1).Interior.ColorIndex
Next r
End Sub

you may have to play with which sheet you are copying from

"klafert" wrote:



"provided to me by Paul who has
been helping me in the worksheet forum:


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 19/05/2005
'

'
mycell = Range("A1").Value
mycolor = Range("C1").Value
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
Sheets("Sheet1").Select
mycell = Range("A2").Value
mycolor = Range("c2")
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
Sheets("Sheet1").Select
mycell = Range("A3").Value
mycolor = Range("C3")
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
End Sub



Here is a list of the modifications which I require to make to the above
macro:

1) Modify it so that instead of having the absolute cell references in the
code, I want it to work on a range of cells in column A (I have 600+ rows to
process, I don't want to have a macro that has a separate section for A1, A2,
A3 .. A600, etc as it would be unmanageable).

2) The macro currently checks a cell in column C and colors a cell on sheet
2, the sheet2 cell reference comes from checking the row A cell value.
However, what I really want to do is query the color of the column cell, and
use that same color for the target cell on sheet 2.


I have not found a way of doing 2) yet, but I have attempted to modify the
macro to do 1) but have failed to come up with anything which doesn't result
in an VB error. Could someone please help me with this?

(I can provide Paul's example spreadsheet/macro if it is helpful to you)


thanks,

Kevin

  #8   Report Post  
kevinm
 
Posts: n/a
Default

hi bj,

almost there ..

in your 2nd example, what you have is very close except there is a problem
with the target location ..

The target location is supposed to be identified by the contents of whatever
is in column1, row n of sheet1, so ..

Lets say we have the following on sheet1 ..

Row1, ColumnA, value = C1
Row1, ColumnC, color = <green

Row2, ColumnA, value = B4
Row2, ColumnC, color = <blue

Row3, ColumnA, value = A7
Row3, ColumnC, color = <red

(the actual number of rows to be processed should be controlled by the loop
variable 'r')

After running the macro I would like to see the following appear on sheet2 ..

Row1, ColumnC, cell colored green
Row4, ColumnB, cell colored blue
Row7, ColumnA, cell colored red

sorry for not stating my requirement clearly, I hope this makes sense,

Kevin


"bj" wrote:

If I read your initial post correct You want to match the cells in Column A
sheet 2 to have the same color as the cells in Column A Sheet 1
The macro probably should have been

Sub try()
'source column Sheet 1 column A (1)
'target column sheet2 column A (1)
For r = 1 To 600
'r represents rows
Sheets("Sheet2").Cells(r, 1).Interior.ColorIndex = Sheets("Sheet1").Cells(r,
1).Interior.ColorIndex
' Cells(1,1) would be the same as A1, Cells(10,2) would be the same
as B10
Next r
End Sub


Then you wouldn't have had to be in Sheet2 to run the macro. Reading your
posting again It may have been column C Sheet 1you wanted to match in this
case make the macro be


Sub try()
'source column Sheet 1 column C (3)
'target column sheet2 column A (1)
For r = 1 To 600
'r represents rows
Sheets("Sheet2").Cells(r, 1).Interior.ColorIndex = Sheets("Sheet1").Cells(r,
3).Interior.ColorIndex
' Cells(1,1) would be the same as A1, Cells(10,2) would be the same
as B10
Next r
End Sub

"kevinm" wrote:

bj,
thanks for your suggestion, sorry to be slow on the uptake but could you
help by explaining how your code fits with the original which I posted when I
started this thread,

Kevin


"bj" wrote:

try something like be in sheet2
Sub try()
For r = 1 To 600
Cells(r, 1).Interior.ColorIndex = Sheets("Sheet1").Cells(r,
1).Interior.ColorIndex
Next r
End Sub

you may have to play with which sheet you are copying from

"klafert" wrote:



"provided to me by Paul who has
been helping me in the worksheet forum:


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 19/05/2005
'

'
mycell = Range("A1").Value
mycolor = Range("C1").Value
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
Sheets("Sheet1").Select
mycell = Range("A2").Value
mycolor = Range("c2")
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
Sheets("Sheet1").Select
mycell = Range("A3").Value
mycolor = Range("C3")
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
End Sub



Here is a list of the modifications which I require to make to the above
macro:

1) Modify it so that instead of having the absolute cell references in the
code, I want it to work on a range of cells in column A (I have 600+ rows to
process, I don't want to have a macro that has a separate section for A1, A2,
A3 .. A600, etc as it would be unmanageable).

2) The macro currently checks a cell in column C and colors a cell on sheet
2, the sheet2 cell reference comes from checking the row A cell value.
However, what I really want to do is query the color of the column cell, and
use that same color for the target cell on sheet 2.


I have not found a way of doing 2) yet, but I have attempted to modify the
macro to do 1) but have failed to come up with anything which doesn't result
in an VB error. Could someone please help me with this?

(I can provide Paul's example spreadsheet/macro if it is helpful to you)


thanks,

Kevin

  #9   Report Post  
bj
 
Posts: n/a
Default

Excel; won't let me insert a macro right now so I haven't tested it , but try

Sub try()
'source column Sheet 1 column A (1)
'target column sheet2 column A (1)
For r = 1 To 600
'r represents rows
tcl=
Sheets("Sheet2").Cells(r, 1).Interior.ColorIndex =
Sheets("Sheet1").Cells(sheets("Sheet1").cells(r,1) .value).Interior.ColorIndex
' Cells(1,1) would be the same as A1, Cells(10,2) would be the same
as B10
Next r
End Sub


"kevinm" wrote:

hi bj,

almost there ..

in your 2nd example, what you have is very close except there is a problem
with the target location ..

The target location is supposed to be identified by the contents of whatever
is in column1, row n of sheet1, so ..

Lets say we have the following on sheet1 ..

Row1, ColumnA, value = C1
Row1, ColumnC, color = <green

Row2, ColumnA, value = B4
Row2, ColumnC, color = <blue

Row3, ColumnA, value = A7
Row3, ColumnC, color = <red

(the actual number of rows to be processed should be controlled by the loop
variable 'r')

After running the macro I would like to see the following appear on sheet2 ..

Row1, ColumnC, cell colored green
Row4, ColumnB, cell colored blue
Row7, ColumnA, cell colored red

sorry for not stating my requirement clearly, I hope this makes sense,

Kevin


"bj" wrote:

If I read your initial post correct You want to match the cells in Column A
sheet 2 to have the same color as the cells in Column A Sheet 1
The macro probably should have been

Sub try()
'source column Sheet 1 column A (1)
'target column sheet2 column A (1)
For r = 1 To 600
'r represents rows
Sheets("Sheet2").Cells(r, 1).Interior.ColorIndex = Sheets("Sheet1").Cells(r,
1).Interior.ColorIndex
' Cells(1,1) would be the same as A1, Cells(10,2) would be the same
as B10
Next r
End Sub


Then you wouldn't have had to be in Sheet2 to run the macro. Reading your
posting again It may have been column C Sheet 1you wanted to match in this
case make the macro be


Sub try()
'source column Sheet 1 column C (3)
'target column sheet2 column A (1)
For r = 1 To 600
'r represents rows
Sheets("Sheet2").Cells(r, 1).Interior.ColorIndex = Sheets("Sheet1").Cells(r,
3).Interior.ColorIndex
' Cells(1,1) would be the same as A1, Cells(10,2) would be the same
as B10
Next r
End Sub

"kevinm" wrote:

bj,
thanks for your suggestion, sorry to be slow on the uptake but could you
help by explaining how your code fits with the original which I posted when I
started this thread,

Kevin


"bj" wrote:

try something like be in sheet2
Sub try()
For r = 1 To 600
Cells(r, 1).Interior.ColorIndex = Sheets("Sheet1").Cells(r,
1).Interior.ColorIndex
Next r
End Sub

you may have to play with which sheet you are copying from

"klafert" wrote:



"provided to me by Paul who has
been helping me in the worksheet forum:


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 19/05/2005
'

'
mycell = Range("A1").Value
mycolor = Range("C1").Value
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
Sheets("Sheet1").Select
mycell = Range("A2").Value
mycolor = Range("c2")
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
Sheets("Sheet1").Select
mycell = Range("A3").Value
mycolor = Range("C3")
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
End Sub



Here is a list of the modifications which I require to make to the above
macro:

1) Modify it so that instead of having the absolute cell references in the
code, I want it to work on a range of cells in column A (I have 600+ rows to
process, I don't want to have a macro that has a separate section for A1, A2,
A3 .. A600, etc as it would be unmanageable).

2) The macro currently checks a cell in column C and colors a cell on sheet
2, the sheet2 cell reference comes from checking the row A cell value.
However, what I really want to do is query the color of the column cell, and
use that same color for the target cell on sheet 2.


I have not found a way of doing 2) yet, but I have attempted to modify the
macro to do 1) but have failed to come up with anything which doesn't result
in an VB error. Could someone please help me with this?

(I can provide Paul's example spreadsheet/macro if it is helpful to you)


thanks,

Kevin

  #10   Report Post  
kevinm
 
Posts: n/a
Default

bj,

I tried your macro but it errors out ..

"Run-time error '13':
Type mismatch"


Also, I don't think that your macro is setting the target cell on sheet 2
correctly ..

You have ..

Sheets("Sheet2").Cells(r, 1)....

However, instead of (r,1) I think we should be plugging in the 'value' read
from the sheet1 column1, row n.

I know it is difficult to debug this when you can run macro in Excel, I hope
you can get it working.

BTW do you know if there is any way of me sending you my example spreadsheet
without you (or I ) divulging our email address? It would be much easier for
you to understand my requirements if I could send you my example spreadsheet.


(this forum doesn't seem to have the option to upload attachments)

Kevin

"bj" wrote:

Excel; won't let me insert a macro right now so I haven't tested it , but try

Sub try()
'source column Sheet 1 column A (1)
'target column sheet2 column A (1)
For r = 1 To 600
'r represents rows
tcl=
Sheets("Sheet2").Cells(r, 1).Interior.ColorIndex =
Sheets("Sheet1").Cells(sheets("Sheet1").cells(r,1) .value).Interior.ColorIndex
' Cells(1,1) would be the same as A1, Cells(10,2) would be the same
as B10
Next r
End Sub


"kevinm" wrote:

hi bj,

almost there ..

in your 2nd example, what you have is very close except there is a problem
with the target location ..

The target location is supposed to be identified by the contents of whatever
is in column1, row n of sheet1, so ..

Lets say we have the following on sheet1 ..

Row1, ColumnA, value = C1
Row1, ColumnC, color = <green

Row2, ColumnA, value = B4
Row2, ColumnC, color = <blue

Row3, ColumnA, value = A7
Row3, ColumnC, color = <red

(the actual number of rows to be processed should be controlled by the loop
variable 'r')

After running the macro I would like to see the following appear on sheet2 ..

Row1, ColumnC, cell colored green
Row4, ColumnB, cell colored blue
Row7, ColumnA, cell colored red

sorry for not stating my requirement clearly, I hope this makes sense,

Kevin


"bj" wrote:

If I read your initial post correct You want to match the cells in Column A
sheet 2 to have the same color as the cells in Column A Sheet 1
The macro probably should have been

Sub try()
'source column Sheet 1 column A (1)
'target column sheet2 column A (1)
For r = 1 To 600
'r represents rows
Sheets("Sheet2").Cells(r, 1).Interior.ColorIndex = Sheets("Sheet1").Cells(r,
1).Interior.ColorIndex
' Cells(1,1) would be the same as A1, Cells(10,2) would be the same
as B10
Next r
End Sub


Then you wouldn't have had to be in Sheet2 to run the macro. Reading your
posting again It may have been column C Sheet 1you wanted to match in this
case make the macro be


Sub try()
'source column Sheet 1 column C (3)
'target column sheet2 column A (1)
For r = 1 To 600
'r represents rows
Sheets("Sheet2").Cells(r, 1).Interior.ColorIndex = Sheets("Sheet1").Cells(r,
3).Interior.ColorIndex
' Cells(1,1) would be the same as A1, Cells(10,2) would be the same
as B10
Next r
End Sub

"kevinm" wrote:

bj,
thanks for your suggestion, sorry to be slow on the uptake but could you
help by explaining how your code fits with the original which I posted when I
started this thread,

Kevin


"bj" wrote:

try something like be in sheet2
Sub try()
For r = 1 To 600
Cells(r, 1).Interior.ColorIndex = Sheets("Sheet1").Cells(r,
1).Interior.ColorIndex
Next r
End Sub

you may have to play with which sheet you are copying from

"klafert" wrote:



"provided to me by Paul who has
been helping me in the worksheet forum:


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 19/05/2005
'

'
mycell = Range("A1").Value
mycolor = Range("C1").Value
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
Sheets("Sheet1").Select
mycell = Range("A2").Value
mycolor = Range("c2")
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
Sheets("Sheet1").Select
mycell = Range("A3").Value
mycolor = Range("C3")
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
End Sub



Here is a list of the modifications which I require to make to the above
macro:

1) Modify it so that instead of having the absolute cell references in the
code, I want it to work on a range of cells in column A (I have 600+ rows to
process, I don't want to have a macro that has a separate section for A1, A2,
A3 .. A600, etc as it would be unmanageable).

2) The macro currently checks a cell in column C and colors a cell on sheet
2, the sheet2 cell reference comes from checking the row A cell value.
However, what I really want to do is query the color of the column cell, and
use that same color for the target cell on sheet 2.


I have not found a way of doing 2) yet, but I have attempted to modify the
macro to do 1) but have failed to come up with anything which doesn't result
in an VB error. Could someone please help me with this?

(I can provide Paul's example spreadsheet/macro if it is helpful to you)


thanks,

Kevin



  #11   Report Post  
kevinm
 
Posts: n/a
Default

Hi bj,

I think I have figured it out, it is not a very elegant bit of coding but
the following seems to work ..


Sub try()
'source column Sheet 1 column A (1)
'target column sheet2 column A (1)
For r = 1 To 3
'r represents rows
mycellValue = Sheets("Sheet1").Cells(r, 1).Value
mycellColor = Sheets("Sheet1").Cells(r, 3).Interior.ColorIndex
Sheets("Sheet2").Range(mycellValue).Interior.Color Index = mycellColor
Next r
End Sub


thanks for all your help,

Kevin



"kevinm" wrote:

bj,

I tried your macro but it errors out ..

"Run-time error '13':
Type mismatch"


Also, I don't think that your macro is setting the target cell on sheet 2
correctly ..

You have ..

Sheets("Sheet2").Cells(r, 1)....

However, instead of (r,1) I think we should be plugging in the 'value' read
from the sheet1 column1, row n.

I know it is difficult to debug this when you can run macro in Excel, I hope
you can get it working.

BTW do you know if there is any way of me sending you my example spreadsheet
without you (or I ) divulging our email address? It would be much easier for
you to understand my requirements if I could send you my example spreadsheet.


(this forum doesn't seem to have the option to upload attachments)

Kevin

"bj" wrote:

Excel; won't let me insert a macro right now so I haven't tested it , but try

Sub try()
'source column Sheet 1 column A (1)
'target column sheet2 column A (1)
For r = 1 To 600
'r represents rows
tcl=
Sheets("Sheet2").Cells(r, 1).Interior.ColorIndex =
Sheets("Sheet1").Cells(sheets("Sheet1").cells(r,1) .value).Interior.ColorIndex
' Cells(1,1) would be the same as A1, Cells(10,2) would be the same
as B10
Next r
End Sub


"kevinm" wrote:

hi bj,

almost there ..

in your 2nd example, what you have is very close except there is a problem
with the target location ..

The target location is supposed to be identified by the contents of whatever
is in column1, row n of sheet1, so ..

Lets say we have the following on sheet1 ..

Row1, ColumnA, value = C1
Row1, ColumnC, color = <green

Row2, ColumnA, value = B4
Row2, ColumnC, color = <blue

Row3, ColumnA, value = A7
Row3, ColumnC, color = <red

(the actual number of rows to be processed should be controlled by the loop
variable 'r')

After running the macro I would like to see the following appear on sheet2 ..

Row1, ColumnC, cell colored green
Row4, ColumnB, cell colored blue
Row7, ColumnA, cell colored red

sorry for not stating my requirement clearly, I hope this makes sense,

Kevin


"bj" wrote:

If I read your initial post correct You want to match the cells in Column A
sheet 2 to have the same color as the cells in Column A Sheet 1
The macro probably should have been

Sub try()
'source column Sheet 1 column A (1)
'target column sheet2 column A (1)
For r = 1 To 600
'r represents rows
Sheets("Sheet2").Cells(r, 1).Interior.ColorIndex = Sheets("Sheet1").Cells(r,
1).Interior.ColorIndex
' Cells(1,1) would be the same as A1, Cells(10,2) would be the same
as B10
Next r
End Sub


Then you wouldn't have had to be in Sheet2 to run the macro. Reading your
posting again It may have been column C Sheet 1you wanted to match in this
case make the macro be


Sub try()
'source column Sheet 1 column C (3)
'target column sheet2 column A (1)
For r = 1 To 600
'r represents rows
Sheets("Sheet2").Cells(r, 1).Interior.ColorIndex = Sheets("Sheet1").Cells(r,
3).Interior.ColorIndex
' Cells(1,1) would be the same as A1, Cells(10,2) would be the same
as B10
Next r
End Sub

"kevinm" wrote:

bj,
thanks for your suggestion, sorry to be slow on the uptake but could you
help by explaining how your code fits with the original which I posted when I
started this thread,

Kevin


"bj" wrote:

try something like be in sheet2
Sub try()
For r = 1 To 600
Cells(r, 1).Interior.ColorIndex = Sheets("Sheet1").Cells(r,
1).Interior.ColorIndex
Next r
End Sub

you may have to play with which sheet you are copying from

"klafert" wrote:



"provided to me by Paul who has
been helping me in the worksheet forum:


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 19/05/2005
'

'
mycell = Range("A1").Value
mycolor = Range("C1").Value
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
Sheets("Sheet1").Select
mycell = Range("A2").Value
mycolor = Range("c2")
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
Sheets("Sheet1").Select
mycell = Range("A3").Value
mycolor = Range("C3")
Sheets("Sheet2").Select
Range(mycell).Select
With Selection.Interior
.ColorIndex = mycolor
.Pattern = xlSolid
End With
End Sub



Here is a list of the modifications which I require to make to the above
macro:

1) Modify it so that instead of having the absolute cell references in the
code, I want it to work on a range of cells in column A (I have 600+ rows to
process, I don't want to have a macro that has a separate section for A1, A2,
A3 .. A600, etc as it would be unmanageable).

2) The macro currently checks a cell in column C and colors a cell on sheet
2, the sheet2 cell reference comes from checking the row A cell value.
However, what I really want to do is query the color of the column cell, and
use that same color for the target cell on sheet 2.


I have not found a way of doing 2) yet, but I have attempted to modify the
macro to do 1) but have failed to come up with anything which doesn't result
in an VB error. Could someone please help me with this?

(I can provide Paul's example spreadsheet/macro if it is helpful to you)


thanks,

Kevin

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 02:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"