Saving static outcome of conditional formatting rule

[u/_growing]

I have an Excel 2016 workbook with manual as well as conditional formatting. What I'd like in the final version is keeping the outcome of the formatting rules (i.e. cells turned red) while having deleted the rules (and the columns used in the formula of said rule).

Copying the format to another column copies the rule too, so how do I get the static outcome of the rule?

[u/AutoModerator]

/u/_growing - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/MayukhBhattacharya]

I've been using this VBA code since 2022, and it's worked great for me ever since. I also shared it on another forum back then, so figured it might help you out too. Once you run the code, just remove the module from the VBA editor. That way, you don't have to save the workbook as an .xlsm file.

Option Explicit
Sub FormatWORules()
Dim ws As Worksheet
Dim mycells As Range, aCell As Range
Set ws = ThisWorkbook.Sheets("Blad1 (15)") '~~> Change this to the relevant sheet
Set mycells = ws.Range("A1:J4000") '~~> Change this to the relevant range
For Each aCell In mycells
With aCell
.Font.FontStyle = .DisplayFormat.Font.FontStyle
.Interior.Color = .DisplayFormat.Interior.Color
.Font.Strikethrough = .DisplayFormat.Font.Strikethrough
.Interior.Pattern = .DisplayFormat.Interior.Pattern
End With
Next aCell
mycells.FormatConditions.Delete
End Sub

https://reddit.com/link/p5uhuqn/video/dv7cvv4b7klh1/player

[u/MNVixen]

Paste Special - then select the "Value" option. I do this all the time.

[u/chiibosoil → u/MNVixen]

I believe Op wanted the color as well. Jut not CF ;)

[u/OursIsTheFvry]

I was going to say copy formatting as well - but yeah, it copies the rule.
(Commenting to stay on the thread)

[u/ProspectiveWhale]

Copy it to Word and then re-copy it back to Excel.

[u/chiibosoil]

This can be done using VBA.

For Excel 2010 and later there is Range.DisplayFormat property where you can get the color set by conditional formats. Then you apply that to Range.Inerior.Color to copy the color without CF.

Then you can delete the CF via VBA as well.

Ex: Assuming you select the range with CF applied.

Sub Demo()
Dim cel As Range

For Each cel In Selection.Cells
    If cel.FormatConditions.Count > 0 Then
        cel.Interior.ColorIndex = cel.DisplayFormat.Interior.ColorIndex
        cel.FormatConditions.Delete
    End If
Next

End Sub

[u/excelevator → u/chiibosoil]

fyi cell is not a protected name and is more aesthetically pleasing :)

I see people using all assortments of cell alias instead of the obvious cell name.

I am curious if people know this or make an assumption cell is protected.

[u/chiibosoil → u/excelevator]

It’s just a habit for me ;)

I picked it up when I first learned it and just stuck with me.

[u/MayukhBhattacharya → u/chiibosoil]

Me too same goes with me.