Function for copying color formatting
 

I am attempting to copy the color formatting from one cell to another on a
different worksheet. I do not see a function for it so I was wondering if
there was some VBA code to do it.

Here is an example of what I am trying to do:

Worksheet A has A1 formatted with red, A2 formatted with yellow, and A3
formatted with green. All which are conditionally formatted.

On worksheet B, I want to reference A1 (or any cell) from worksheet A which
would include the contents and color.

Any help is appreciated!

Rich

Function for copying color formatting
 

This is only partially useful:

Sub format_painter()
Dim r, r2 As Range
Dim s As String
Dim b As Boolean
For Each r In Selection
If r.HasFormula Then
s = r.Formula
s = Right(s, Len(s) - 1)
On Error Resume Next
Set r2 = Range(s)
On Error GoTo 0
b = Not r2 Is Nothing
If b Then
r2.Copy
r.PasteSpecial Paste:=xlPasteFormats
End If
End If
Next
End Sub

If you select a cell that has a direct reference like:
=Sheet2!A3
and run the macro, it will go back to Sheet2, cell A3, copy and format-paste
it to the target. I say partially successful because it can't copy
complicated formats.
--
Gary's Student


"Rich Young" wrote:

I am attempting to copy the color formatting from one cell to another on a
different worksheet. I do not see a function for it so I was wondering if
there was some VBA code to do it.

Here is an example of what I am trying to do:

Worksheet A has A1 formatted with red, A2 formatted with yellow, and A3
formatted with green. All which are conditionally formatted.

On worksheet B, I want to reference A1 (or any cell) from worksheet A which
would include the contents and color.

Any help is appreciated!

Rich

Function for copying color formatting
 

Thanks Gary, I will give it a try!

"Gary''s Student" wrote:

This is only partially useful:

Sub format_painter()
Dim r, r2 As Range
Dim s As String
Dim b As Boolean
For Each r In Selection
If r.HasFormula Then
s = r.Formula
s = Right(s, Len(s) - 1)
On Error Resume Next
Set r2 = Range(s)
On Error GoTo 0
b = Not r2 Is Nothing
If b Then
r2.Copy
r.PasteSpecial Paste:=xlPasteFormats
End If
End If
Next
End Sub

If you select a cell that has a direct reference like:
=Sheet2!A3
and run the macro, it will go back to Sheet2, cell A3, copy and format-paste
it to the target. I say partially successful because it can't copy
complicated formats.
--
Gary's Student


"Rich Young" wrote:

I am attempting to copy the color formatting from one cell to another on a
different worksheet. I do not see a function for it so I was wondering if
there was some VBA code to do it.

Here is an example of what I am trying to do:

Worksheet A has A1 formatted with red, A2 formatted with yellow, and A3
formatted with green. All which are conditionally formatted.

On worksheet B, I want to reference A1 (or any cell) from worksheet A which
would include the contents and color.

Any help is appreciated!

Rich