Copying the colors of one Matrix to Another?!

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

Use the offset command to color the corresponding cells in the other range and it should do the trick.
I noticed that once you set the interior color in your select case statement it remained that color until it was reassigned by the next select case. I added a 4th select case statement that will change the interior color to 'No Fill' if the number is not 2 or 4 or empty "".
Also, FYI interior color 2 is white not no color or 'No Fill'

Please Login or Register  to view this content.

HTH

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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 [email protected]


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" <[email protected]> wrote in message
news:[email protected]...
> 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 [email protected]
>
>
> 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.

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

--
Regards,
Tom Ogilvy

"Mike" <[email protected]> wrote in message
news:[email protected]...
> 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