Copying Fill Colors using a Macro

[mhp620]

Could someone give some tips of copying just "fill" colors in a macro? I want to copy the fill colors in column 1 of a worksheet to all the columns across the worksheet.The rows have different fill colors.

Thanks in advance.

Mike

 

[VoG]

Maybe like this

Sub Test2()
Dim LR As Long, LC As Long
Dim i As Long, j As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
LC = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
For j = 2 To LC
    For i = 1 To LR
        Cells(i, j).Interior.ColorIndex = Cells(i, 1).ColorIndex
    Next i
Next j
End Sub

 

[VoG]

Or faster(?)

Sub Test2()
Dim LR As Long, LC As Long
Dim i As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
LC = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
For i = 1 To LR
    Range(Cells(i, 2), Cells(i, LC)).Interior.ColorIndex = Cells(i, 1).ColorIndex
Next i
End Sub

 

[Rick Rothstein]

Or faster(?)

Sub Test2()
Dim LR As Long, LC As Long
Dim i As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
LC = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
For i = 1 To LR
    Range(Cells(i, 2), Cells(i, LC)).Interior.ColorIndex = Cells(i, 1).Interior.ColorIndex
Next i
End Sub

Mike,

Peter accidentally left off the above indicated (in red) property reference.

 

[VoG]

Rick

I have to thank you once again for correcting my slapdash coding.

Thanks again.

 

[Rick Rothstein]

I have to thank you once again for correcting my slapdash coding.

Thanks again.

You are welcome, of course, but you don't have to thank me for doing something like that... we all watch each others' backs here, right?

 

[VoG]

Rick

I sometimes post air code (which I shouldn't) but you catch my errors. Greatly appreciated.


I note that you are an M$ MVP (well deserved) so I always value your contributions.

 

[Rick Rothstein]

I sometimes post air code (which I shouldn't) but you catch my errors. Greatly appreciated.

Again, you are quite welcome. As for "air code", I know what you mean. Almost everytime I decide the question is so easy that I don't need to check my code/formula solutions, it seems someone responds with a correction to what I posted.

I note that you are an M$ MVP (well deserved) so I always value your contributions.

Thank you! October 1st is fast approaching so I will know in a couple of days or so if my Microsoft MVP status will be renewed (I'm not sure if my increasing contributions in this forum count when Microsoft considers such things or not).

 

[mhp620]

Thanks!! Works like a charm. Mike