Is it possible to conditionally format the third decimal of a number in Excel?

Question

I have a series of formulas that are returning values with three decimal places. Since these are dollar amounts, and I do want to see the last decimal, I was hoping there would be a way to format the third decimal so that the rest of the value looks more like a dollar figure. For instance, instead of seeing this:

1.675

I'd like to see this

1.675

Or even better, show that final digit, the "5" in this case, as unbolded medium yellow so that "1.67" becomes the focus and the "5" becomes a little additional information.

I've been studying the number formatting and the conditional formatting, and thus far I don't think it's possible. But I'm hoping someone can prove my assertion wrong and show me the way.

Answer 1

You mention researching number formatting, which is probably the most seamless option. You can try a custom number format like this "0.00(0)" or this "0.00 0" which will show values as "1.23(4)" and "1.23 4". I don't think you can set character specific colors though using this method.

As Teylyn points out, you can do this with VBA, but you have to set your cell NumberFormat to text which is annoying. What I would do is make the macro toggle itself and add it to the quick access toolbar so you can easily toggle it on and off. Here is an example that toggles the format for cell A1.

Sub ToggleSpecialFormat()
If Range("A1").NumberFormat = "@" Then
    Range("A1").NumberFormat = "General"
    Range("A1").Formula = Range("A1").Formula
Else
    Range("A1").NumberFormat = "@"
    Range("A1").Formula = Range("A1").Formula
    Range("A1").Characters(5, 1).Font.Color = vbRed
End If
End Sub