Copy & Paste Special Conditionally formatted coloured cells

Claire Jackson

New Member
Joined
Jun 30, 2020
Messages
41
Office Version
  1. 2016
Platform
  1. Windows
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
 

Attachments

  • Extract.jpg
    Extract.jpg
    140.5 KB · Views: 4

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

New Member
Joined
Jun 30, 2020
Messages
41
Office Version
  1. 2016
Platform
  1. Windows
Hi, that didn't work because the conditional format in the cell still takes over
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows
You would need to remove the CF, otherwise there is no point in changing the fill colour.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Excel Formula:
=GET.CELL(63,INDIRECT("rc",FALSE))
then change your CF rules to include a check of IsColoured
+Fluff New.xlsm
ABC
1CustomerProductRevenue
2BobApple10
3BobOrange4
4AnnieBanana8
5SamApple7
6SamFig3
7KatieBanana8
8KatieGrapes5
Contacts
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C2:C8Expression=AND(IsColoured=0,C2<8)textNO
 

Claire Jackson

New Member
Joined
Jun 30, 2020
Messages
41
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,921
Messages
5,639,016
Members
417,062
Latest member
CM214

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top