VBA copy background color

[frankrss]

Hi, I want to copy the background color in column B to column A, but the column B uses conditional formatting to get the background color. How can I use macro to copy the color?
See attached file.

https://docs.google.com/spreadsheet/ccc?key=0AqhsfIOFA5jHdHNUek5xaERjeGhub3ROdlRnWkdocmc&hl=en_US

Thanks

 

[Trevor G]

This would do the copy and paste special method. But if you have conditional formatting, why not extend it to include column A based on the results in Column B

Sub Macro1()
'Try this out
Columns("B:B").Copy
Columns("A:A").Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
End Sub

 

[frankrss]

Hi, Trevor, thanks for you reply.
I have many sheets with this kind of things, and there will allways be changes. I think it will be easier to use VBA to do it before I close the file.
Do you have a way by useing vba to copy the background color?

Thanks
Frank

 

[Trevor G]

Frank you apply what I have done into an Array.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Macro1()<br><SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> Sheets(Array("Sheet1", "Sheet2", "sheet3"))<br>ws.Activate<br>    Columns("B:B").Copy<br>    Columns("A:A").Select<br>    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=<SPAN style="color:#00007F">False</SPAN><br>    Range("A1").Select<br>    Application.CutCopyMode = False<br><SPAN style="color:#00007F">Next</SPAN> ws<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[frankrss]

Hi, Trevor, this code will not copy the real background color, maybe you can have a test in the file which I gave here.

 

[Trevor G]

Look at this website to see if it helps.

http://www.cpearson.com/excel/cformatting.htm

When I have used the code it works perfectly. I set Conditional formatting and applied a rule it works.

Excel Workbook
	C	D
1	Data	Coloured
2	45
3	45
4	67
5	89
6	12
7	34
Sheet3

Conditions are:

=$C2<40 then red
=$C2>56 then green

Code used

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Macro2()<br><SPAN style="color:#007F00">'One sheet copy conditional formatting colour</SPAN><br>    Columns("C:C").Copy<br>    Columns("D:D").Select<br>    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=<SPAN style="color:#00007F">False</SPAN><br>    Range("A1").Select<br>    Application.CutCopyMode = False<br><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[frankrss]

I tried, Sorry, it does not work with my file, can you please have a test in my file, then you will know what I mean.

Thanks
Frank

 

[Trevor G]

Frank I have copied your data, can you please show the Conditional Formatting Options you have applied. Or send a PM and give me an email address.

 

[frankrss]

Please download this file.

https://docs.google.com/spreadsheet/ccc?key=0AqhsfIOFA5jHdHNUek5xaERjeGhub3ROdlRnWkdocmc&hl=en_US

and open it in Excel.

 

[gregtx81]

Loop each cell in the intersection of usedrange and column A setting


For the cells with conditional formatting in column B:
cellColA.interior.color=cellColA.offset(,1).formatCondtions(1).interior.color

if there's no conditional formatting in column b then
cellColA.interior.color=cellColA.offset(,1).interior.color