copy color from one sheet to another, not with CF

[squirrellydw]

Is there away I can copy color from one sheet to another without using conditional formatting because I don't think it will work. What I'm trying to do is have a list of names on one sheet and whenever that name is listed anywhere on another sheet have it show up as red. thanks

 

[kevin9999]

What you describe should be easy to achieve through Conditional Formatting. Could you post your 2 sheets - the one with the list of names, and the one you want assessed - using the XL2BB - Excel Range to BBCode?

 

[kvsrinivasamurthy]

Try. This code will copy value and color only. This will not copy CF. Copies from A2:A10 To G2:G10.

Sub copyColorOfCF()
Dim Rng As Range, DestRngb As Range, cel As Range
Dim Ro&
Set Rng = Range("A2:A10")
Set DestRng = Range("G2")
For Each cel In Rng
With DestRng.Offset(Ro, 0)
.Value = cel.Value
.Interior.Color = cel.DisplayFormat.Interior.Color
End With
Ro = Ro + 1

Next cel

End Sub

 

[kvsrinivasamurthy]

I have included Sheet names.
This code will copy value and color only. This will not copy CF. Copies from Sheet2 A2:A10 To Sheet3 G2:G10.

Sub copyColorOfCF()
Dim Rng As Range, DestRng As Range, cel As Range
Dim Ro&
Set Rng = Sheets("Sheet2").Range("A2:A10")
Set DestRng = Sheets("Sheet3").Range("G2")
For Each cel In Rng
With DestRng.Offset(Ro, 0)
.Value = cel.Value
.Interior.Color = cel.DisplayFormat.Interior.Color
End With
Ro = Ro + 1

Next cel

End Sub

 

[squirrellydw]

I will give this a try later this week. I have about 15 sheets in the workbook that this needs to work on and needs to cover A2 to P40