Conditional Formatting: Custom Formatting code not applying to text.

Question

I have a simple conditional formatting rule to evaluate a cell for text equal to an "x" and then apply a custom format of simple text "✓" to replace the content of the cell with a unicode "checkmark".  The background color of the format was set to a light blue to confirm that the formula works.

I have formatting applying when the cell contains the text "x" (lower case x).

The applied format:

• A blue background fill
• The custom formatting code: **"**✓"

The purpose of this format is the allow entries of "x" to render as a checkmark.  Currently, only the "blue background" fill applies when an "x" is present in the cell.  The custom formatting does not.  If I change the criteria to a number match, for example "the cell value = 1", then the custom formatting and the background both apply correctly.  Is this expected behavior?  I can apply the custom format manually to the cells and they do in fact render checkmarks in place of the cell contents, so it's not that the format does not work with text, but rather conditional formatting appears to not apply custom format codes to text values.

Just as a side note, yes I am aware of the custom formatting pre-built "Icons", and quite frankly they suck and are too inflexible so I'd rather not use them.

Thanks in advance to anyone who can offer any advice here.

Answer 1

You could use a column of formulas to display check marks based on another column (format with WingDings)

=IF(A2=1,CHAR(252),"")

=IF(A2="x",CHAR(252),"")

Answer 2

Thanks, I appreciate the effort.  That definitely works.  I should have included it in my original prose, but I am trying to stay away from VBA for this.  Specific use-case being that it needs to still function in the web-application excel.  Sorry for if you feel you wasted your time!  Same to you Bernard.  Again I very much appreciate the help though.

Edit: I should also note that I have decided to implement it using an actual number (for example, 1=Check Mark).  Not ideal, but it works.

Answer 3

Just to add to Bernie's sub. If you want to change interior color:

best wises

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("A2:A100")) Is Nothing Then Exit Sub  'Specific range

    'Turn off events to keep out of loops

    Application.EnableEvents = False

If Target.Value = "x" Then

        Target.Value = Chr(252)

        Target.Font.Name = "Wingdings"

        Target.Interior.ColorIndex = 5 'blue

    Else

         Target.Font.Name = "Verdana"

       Target.Interior.ColorIndex = 0 'white

    End If

    'Turn events back on to get ready for the next change

    Application.EnableEvents = True

End Sub

Answer 4

I like using the change event:

1. Copy this code.
2. Right-Click the sheet tab of interest.
3. Select "View Code"
4. Paste the code into the window that appears.
5. Save the file as a macro-enabled .xlsm file.
6. Make changes to the range and default font name as needed

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("A2:A100")) Is Nothing Then Exit Sub  'Specific range

    'Turn off events to keep out of loops

    Application.EnableEvents = False

    If Target.Value = "x" Then

        Target.Value = Chr(252)

        Target.Font.Name = "Wingdings"

    Else

         Target.Font.Name = "Verdana"

    End If

    'Turn events back on to get ready for the next change

    Application.EnableEvents = True

End Sub