Removing Conditional Formatting but leaving formatting

[Mo_UK]

Hello, this is my first post so I hope I've included the relevant information. I have searched chandoo and the web but couldn't seem to find a solution - please point me to it if I've missed the answer already.

I've inherited a very large spreadsheet which contains a lot of conditional formatting. Much of this is no longer needed so I want to remove the conditional formatting (which I can do!) but replace it with the formatting that the conditions have resulted in.

So for example if the conditional formatting has resulted in the cell being highlighted yellow with a red font I want to remove the conditional formatting but keep that cell yellow with red text. There are many, many conditions affecting a large number of ranges so it's not really feasible to remove each one individually then reformat.

Any suggestions on how to get the formatting "locked"?

Many thanks
Mo_UK

 

[BBD]

Hi Mo_UK,

Conditional formatting is a pain. A year back, I wanted one of my VBA codes to identify the format based on conditional formatting. Searched the web and could only find the solution of re-caclulcating the condition through VBA and identify the effect on the cell. I finally had to give up since I had a large range of data and numerous conditional formatting rules.

After seeing your post, I began exploring in VBA and the good news is I made a solution to it now.

Here is the code for it. Put it in a VBA module, SELECT the data and run it

Sub Keep_Format()
dim cell as Range
For Each cell In Selection
With cell
  .Font.FontStyle = .DisplayFormat.Font.FontStyle
  .Interior.Color = .DisplayFormat.Interior.Color
  .Font.Strikethrough = .DisplayFormat.Font.Strikethrough
End With
Next cell
End Sub

Note: This might take a little time to process. But should work fine

Cheers,
BD

 

[SirJB7]

Hi, Mo_UK!

You can retrieve CF conditions for each cell in a range, store them in a helper area and then apply them from VBA. It's proportionally harder to the type of different conditions to be checked, since there're 6 basic types:
- to all cells depending its values, 4 styles: 2 color scale, 3 color scale, data bar, icons set
- to only cells that contain a certain value
- to only lower or higher values
- to only lower or higher values than the average: 10 sub-conditions
- to only unique or duplicate values
- use a formula

A few weeks or months ago, I started doing something alike (that I don't happen to find now, here, at chandoo) which would server you as a kickoff of at least just to evaluate what your facing. If I stump on it I'll get back to you, but my humble advice is simple: forget about it, unless you have a few CF conditions.

Regards!

 

[BBD]

@SirJB7 ,
Hi,

In the procedure that I have updated above, VBA reads the displayformat of the cell and assigns the same to the normal format of the cell.

This essentially eliminates the testing of CF conditions through VBA. It did work for me on sample data. And I am pretty sure that it wont work on Data bars, Color scales and icon sets. But it should work on interior color modification and font style modification through CF.

Though no clue how it would respond to large data.

Let me know what you think.

 

[SirJB7]

@BBD
Hi!
Tomorrow I'll give a look at it, today I just went thru without almost ever reading it in detail.
Regards!

 

[Ghazanfar J]

After some frustration I was able to get this done with the Office Clipboard in Excel.. http://www.bookkempt.com/2017/08/remove-conditional-formatting-but-keep.html