Hi everyone,

Say I have a matrix:

2 5
4 1
2 2
4 4
3 2

The following piece of code will color each cell in that matrix:

Dim RngA As Range
Dim RngAInput As Variant

Set RngAInput = Range("B3:c7")
For Each RngA In RngAInput
'Determine the color
Select Case RngA.Value
Case Is = 4: Num = 40
Case Is = 2: Num = 35
Case Is = "": Num = 2
End Select
RngA.Interior.ColorIndex = Num
RngA.Font.ColorIndex = 1
Next RngA

Note that for values of 1, 3, and 5, Conditional Formatting is used
(this is why they are excluded in the code).

NOW, say I have identical blank matrix, Range("b19:c23"), that I want
to copy to it the colors of the above one.

What changes I need to make to the above code to make this happen?

Thanks alot,
Mike

Mike

you can't use the existing code because that is looking for values to change
the interior colour. Why not just record a macro that copies the existing
matrix and then does a Paste Special | Formats to the new blank matrix.
That won't pick up the colours generated by the conditional formatting
though. If you want that, you'd be better removing the Conditional
Formatting and including the other values in code.

Regards

Trevor


"Mike" wrote in message
oups.com...
Hi everyone,

Say I have a matrix:

2 5
4 1
2 2
4 4
3 2

The following piece of code will color each cell in that matrix:

Dim RngA As Range
Dim RngAInput As Variant

Set RngAInput = Range("B3:c7")
For Each RngA In RngAInput
'Determine the color
Select Case RngA.Value
Case Is = 4: Num = 40
Case Is = 2: Num = 35
Case Is = "": Num = 2
End Select
RngA.Interior.ColorIndex = Num
RngA.Font.ColorIndex = 1
Next RngA

Note that for values of 1, 3, and 5, Conditional Formatting is used
(this is why they are excluded in the code).

NOW, say I have identical blank matrix, Range("b19:c23"), that I want
to copy to it the colors of the above one.

What changes I need to make to the above code to make this happen?

Thanks alot,
Mike

Hi Mike,

Providing its OK to paste all formats I would go with Trevor's suggestion.
Otherwise you could try something like this. Not efficient for frequent use.

Sub Test()
Dim RngA As Range
Dim RngAInput As Range
Dim RngBCopy As Range
Dim RowOff As Long
Dim ColOff As Long
Dim Num As Long
Set RngAInput = Range("B3:c7")
Set RngBCopy = Range("B19")
RowOff = RngBCopy.Row - RngAInput.Row
ColOff = RngBCopy.Column - RngAInput.Column
For Each RngA In RngAInput
'Determine the color
Num = xlNone ' ?
Select Case RngA.Value
Case Is = 4: Num = 40
Case Is = 2: Num = 35
Case Is = "": Num = 2 ' maybe not needed ?
End Select
With Union(RngA, RngA.Offset(RowOff, ColOff))
.Interior.ColorIndex = Num
.Font.ColorIndex = 1
End With
Next RngA
End Sub

Obviously this does not cater for CF's in the second range, but can't see
the point of CF in this usage.
Regards,
Peter T

"Mike" wrote in message
oups.com...
Hi everyone,

Say I have a matrix:

2 5
4 1
2 2
4 4
3 2

The following piece of code will color each cell in that matrix:

Dim RngA As Range
Dim RngAInput As Variant

Set RngAInput = Range("B3:c7")
For Each RngA In RngAInput
'Determine the color
Select Case RngA.Value
Case Is = 4: Num = 40
Case Is = 2: Num = 35
Case Is = "": Num = 2
End Select
RngA.Interior.ColorIndex = Num
RngA.Font.ColorIndex = 1
Next RngA

Note that for values of 1, 3, and 5, Conditional Formatting is used
(this is why they are excluded in the code).

NOW, say I have identical blank matrix, Range("b19:c23"), that I want
to copy to it the colors of the above one.

What changes I need to make to the above code to make this happen?

Thanks alot,
Mike

Trevor,

Thanks alot, yes that will do. Thanks to Peter too.

Mike

Trevor/Peter,

Well, I have a problem here. When I applied Trevor idea on a new sheet,
it did work well and thought the problem is resolved!

But, when I created a Macro to copy colors of one matrix into another,
the colors are no longer same as in the original matrix!? For example,
the RED cell becomes light green in the nex matrix!

Let me know if I can email you the file itself. It is small one anyhow?

My email is


Mike

Hi Mike,

Maybe related to those CF's. For testing clear them all and work from there.
If that doesn't work post again and I'll drop you a line.

Regards,
Peter T

"Mike" wrote in message
ups.com...
Trevor/Peter,

Well, I have a problem here. When I applied Trevor idea on a new sheet,
it did work well and thought the problem is resolved!

But, when I created a Macro to copy colors of one matrix into another,
the colors are no longer same as in the original matrix!? For example,
the RED cell becomes light green in the nex matrix!

Let me know if I can email you the file itself. It is small one anyhow?

My email is


Mike

Peter,

Actually, I did remove all the CFs before I did the testing but was
suprised that some colors wont be copied right!?

I tried this format copying on a new sheet and worked fine!

Mike

Mike,
Did you try simply using the offset command that I suggested? It
should accomplish what you desire with very little change to your
original code.
You could even add an input so the user could select how far they want
the output offset from the source.


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=391080

Perhaps, if you are copying between two different workbooks, the color
pallete is different between the two workbooks.

--
Regards,
Tom Ogilvy

"Mike" wrote in message
oups.com...
Peter,

Actually, I did remove all the CFs before I did the testing but was
suprised that some colors wont be copied right!?

I tried this format copying on a new sheet and worked fine!

Mike

bhofsetz,

Could you please re-write my above piece of code by assuming same cell
locations above? I tried the above code you listed but somehow is not
working!

Thanks indeed,
Mike

Tom,

I am actually doing eveything on one single sheet!

Mike