Copy interior color of cell

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,648
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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,648
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

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
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_

Well-known Member
Joined
Nov 9, 2009
Messages
567
No need tot copy:

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

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,648

ADVERTISEMENT

Hello snb,

I tried the following code:

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
 
L

Legacy 98055

Guest
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

Well-known Member
Joined
Jul 23, 2002
Messages
1,648

ADVERTISEMENT

Hello Tom,

I am trying this or am I way off base?

Code:
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
 
L

Legacy 98055

Guest
"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

Well-known Member
Joined
Jul 23, 2002
Messages
1,648
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
 
L

Legacy 98055

Guest
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.
 
Last edited by a moderator:

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,964
Members
413,955
Latest member
FalcoDaz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top