Copy & Paste Special Conditionally formatted coloured cells

[Claire Jackson]

Hi,
I have an excel document that has some conditional formatting in which turns cells green based on another cell.

Basically what I want to do is copy just the green cells and paste special so the cells stay green no matter what data is entered. Is there a way to do this ?

I've attached a jpeg of the spreadsheet.

Thanks in advance

 

[Fluff]

How about

Sub ClaireJackson()
   Dim Cl As Range
   
   For Each Cl In Range("J6:AA20")
      If Cl.DisplayFormat.Interior.Color = 5296274 Then Cl.Interior.Color = 5296274
   Next Cl
End Sub

Change range & colour to suit.

 

[Claire Jackson]

Hi, that didn't work because the conditional format in the cell still takes over

 

[Fluff]

You would need to remove the CF, otherwise there is no point in changing the fill colour.

 

[Fluff]

It is possible to overrule the CF colourlike
In the name manager create a new name (I've called it IsColoured) & in the refers to box put

=GET.CELL(63,INDIRECT("rc",FALSE))

then change your CF rules to include a check of IsColoured

+Fluff New.xlsm
	A	B	C
1	Customer	Product	Revenue
2	Bob	Apple	10
3	Bob	Orange	4
4	Annie	Banana	8
5	Sam	Apple	7
6	Sam	Fig	3
7	Katie	Banana	8
8	Katie	Grapes	5
Contacts

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C8	Expression	=AND(IsColoured=0,C2<8)	text	NO

 

[Claire Jackson]

Great, thank you I'll give that a try but I think i've sorted it anyway. But I will see which way I prefer. Thanks again

 

[Fluff]

You're welcome & thanks for the feedback.