Copy interior color of cell

[Kurt]

I have some cells that change colors based on output.

The paste and paste special functions don't always copy the correct color of the cell.

Has anyone run across this before and what is the correct code to do this?

Kurt

 

[Kurt]

Is there a way to take some code something like this and make it easier?

[code
Sub CopyColor2()
Dim rReply As Range, rCell As Range
Dim lCol As Long

Set rReply = Application.InputBox _
(Prompt:="Selct a single cell that has the background color you wish to copy", Type:=8)

lCol = rReply.Interior.ColorIndex

For Each rCell In ActiveSheet.UsedRange
If rCell.Interior.ColorIndex = lCol Then
rCell.EntireRow.Copy Destination:=Worksheets("Report").Range("K8").End(xlUp)(2, 1)
End If

Next rCell
End Sub
[/code]

Any help is greatly appreciated.

I just want to copy the contents of a range, use the cell's current color and copy it to the sheet report.

Thanks,

Kurt

 

[ravishankar]

Hi
you don't need a macro to copy formats. Select cell Say A2. Click on Format painter Icon (paint brush). select the range you wish to paste it to. If this is not what you are looking for, pl explain why colors should change.If the color is from conditional formatting, it won't get copied.
Ravi

 

[snb_]

No need tot copy:

sub samecolor()
  sheets(1).cells(3,8).interior.colorindex=sheets(1).cells(10,8).interior.colorindex 
End Sub

 

[Kurt]

Hello snb,

I tried the following code:

Sub samecolor()
  'Sheets("PowerAnalysis").Cells(3, 8).Interior.ColorIndex = Sheets(1).Cells(10, 8).Interior.ColorIndex
  Sheets("PowerAnalysis").Cells(24, 11).Interior.ColorIndex = Sheets("Report").Cells(1, 5).Interior.ColorIndex
End Sub
[code]

It didn't work.  Do you or anyone else have any ideas?

Thanks,


Kurt

 

[Legacy 98055]

If your cells are colored by way of conditional formatting; see the FormatConditions Property in VBA help. When you apply conditional formatting, your cell may now have several interior colors. For example, if range A1 has an interior color of blue and conditional formatting applied that shades it as red, you now have at lease two interior colors and you must distinguish between what color you are after.

Example:
Range("A1").Interior.ColorIndex
Range("A1").FormatConditions.Item(1).Interior.ColorIndex

Where Item(1) refers to the first condition applied.

 

[Kurt]

Hello Tom,

I am trying this or am I way off base?

Sub samecolor2()
    Sheets("PowerAnalysis").Cells(11, 24).FormatConditions.Item(1).Interior.ColorIndex = Sheets("Report").Cells(1, 5).Interior.ColorIndex
End Sub

Any thoughts from you or anyone else?

Thanks,

Kurt

 

[Legacy 98055]

"I have some cells that change colors based on output."

I am assuming that you are using conditional formatting on some or all of your cells. Are you?

"The paste and paste special functions don't always copy the correct color of the cell."

Are you wishing to paste conditional formats as well? I don't know what you are trying to do.

 

[Kurt]

Hello Tom,

No the conditional formats don't have to be pasted however, the colors change and the report page needs to reflect that.

Many thanks for your time and efforts.

Kurt

 

[Legacy 98055]

Ok. I see, I think. You want to copy the "Visible Format" and the data, but not any conditional formats. Is this correct?

I need to know what you are copying over to the destination range in order to help you. In any case, there is not any native ability to do this in Excel that I know of. You will need code.

What version of Excel are you using? I am not familiar with 2007.