Have you ever had to convert conditional formatting on a sheet?

It happened to me in more than one occasion.

Usually when I had a nice sheet with all the bells and whistles, all conditional formatting working and colorful, then I had to send a value only version of this sheet to someone.

(valued only means that I don’t want the person to see my formulas, just the resulted values in the cells. Usually we would just copy a selection then paste it back in place but with the special paste values only)

But that would mess up my conditional formatting.

To solve that here is a piece of code you can paste in to your workbook, this will convert conditional formatting on a specified sheet and range.

Sub ConvertConditionalFormatting()

    Dim ws As Worksheet
    Dim mySelection As Range, theCell As Range

    'Here you can set the sheet you want this to work on
    Set ws = Sheet1
    
    'Here you can change the range you want this work on
    Set mySelection = ws.Range("A1:A10")

    For Each theCell In mySelection
        With theCell
          .Font.FontStyle = .DisplayFormat.Font.FontStyle
          .Font.Color = .DisplayFormat.Font.Color
          .Interior.Color = .DisplayFormat.Interior.Color
        End With
    Next theCell

    mySelection.FormatConditions.Delete

End Sub

How to add this code to your workbook?

For detailed steps on how to use this code in your workbook please visit
this post where we discuss it in depth how to paste in the code.

What can I change in this code?

The two line you should change to suit your requirements are the ones after the comments
(Note: Comments in VBA start with an ‘. Anything after that character will not get run as a code)

‘Here you can set the sheet you want this to work on
Set ws = Sheet1

‘Here you can change the range you want this work on
Set mySelection = ws.Range(“A1:A10“)

How is the code working?

Here is a picture before:
You can see that the cells in A1:A10 have a conditional formatting
(Just a simple color gradient)

Conditional formatting on sheet
Color gradient conditional formatting on sheet

Then we add in the code and run it.

Conditional formatting converted to colors
Conditional formatting converted to colors and styles.

So, there you have it.
With the code above you can convert your pretty sheets to a solid one.

Want to know who is behind ExcelAngel? You can find the story behind this website here.

We have a facebook page, it would be great if you could like our page, it help us a ton.

[et_pb_ccfcm_facebook_comments_module ccfcm_app_id=”339898187197719″ _builder_version=”4.6.1″ _module_preset=”default”][/et_pb_ccfcm_facebook_comments_module]