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

 

[squirrellydw]

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

I can't get this to work for some reason. Any chance you could upload a file with it already added?

 

[squirrellydw]

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

OK I got it working but it's not doing what I want. I want to have a list of names on Sheet1 A3 to A23 that are in RED or whatever color I want. Then whenever that name is listed on Sheet2 it copies over the RED color. As a bonus it would be nice if it could copy over to other sheets that I make also, for example sheets3 to sheets 15

 

[squirrellydw]

Attached are two screenshots of what I would like. So anytime and anywhere the names from sheet1 show up on sheet 2 I would like them to copy the same color over automatically

 

[kevin9999]

If your desired format is the same for all cells on each sheet, I'd still be tempted to use Conditional Formatting, something like this:

Book1
	A	B
2
3	Name1
4	Name2
5	Name3
6	Name4
7	Name5
8	Name6
9	Name7
10	Name8
11	Name9
12	Name10
13	Name11
14	Name12
15	Name13
16	Name14
17	Name15
18	Name16
19	Name17
20	Name18
21	Name19
22	Name20
23
Names

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2
3		Name1											Name13
4
5							Name2
6		Name4								NAME X
7											Name14
8		Name6
9														NAME X
10		Name8		Name22		Name7			NAME X
11
12
13														Name8	NAME X
14		Name12			NAME X				Name10
15
16
17												Name9
18
19												Name11
20							Name15				NAME X
21
22
23						Name25
24
25
26
27										Name7
28
29
30
31
32
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:P40	Expression	=COUNTIF(Names!$A$3:$A$22,A2)>0	text	NO

 

[squirrellydw]

If your desired format is the same for all cells on each sheet, I'd still be tempted to use Conditional Formatting, something like this:

Book1
	A	B
2
3	Name1
4	Name2
5	Name3
6	Name4
7	Name5
8	Name6
9	Name7
10	Name8
11	Name9
12	Name10
13	Name11
14	Name12
15	Name13
16	Name14
17	Name15
18	Name16
19	Name17
20	Name18
21	Name19
22	Name20
23
Names

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2
3		Name1											Name13
4
5							Name2
6		Name4								NAME X
7											Name14
8		Name6
9														NAME X
10		Name8		Name22		Name7			NAME X
11
12
13														Name8	NAME X
14		Name12			NAME X				Name10
15
16
17												Name9
18
19												Name11
20							Name15				NAME X
21
22
23						Name25
24
25
26
27										Name7
28
29
30
31
32
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:P40	Expression	=COUNTIF(Names!$A$3:$A$22,A2)>0	text	NO

maybe I don't understand something but I didn't think I could do that easily with CF, I though I have to have a rule for each name?