Anomaly with VBA and Conditional Formatting - Excel Bug?

Question

I have an issue in Excel where conditional formatting produces different results if a subset of the characters in the cell have a different colour, depending on whether

the colour was applied with manual editing or using VBA
if applied with VBA, whether the range of coloured characters includes the first character in the cell

I am using Excel in Office 365 on Windows 10, both fully updated as of today.

In the screenshot below cells A1:A6 have the conditional formatting rule

=NOT(ISBLANK(B1))

In cells A2 and A3 I set first three characters to red by editing manually. In A3 the first three characters remain red when conditional formatting is triggered.

In cell A4 I set the first three characters to red text using the macro

Sub RedFirst()
    ActiveCell.Characters(1, 3).Font.Color = vbRed
End Sub

Instead of getting the same result as A3 when conditional formatting is triggered, the first three characters turn green (the conditional format) instead of remaining red, and the rest of the text remains black instead of being green, although all the text is underlined from the conditional formatting.

In cell A5 I set the second and third characters to red text using the macro

Sub RedNext()
    ActiveCell.Characters(2, 2).Font.Color = vbRed
End Sub

Now, the characters whose colour was set with the macro remain red when conditional formatting is triggered, the same as when I change the text colour manually. Cell A6 shows the result of conditional formatting without having applied a different colour to any text.

I think A3 and A4 should look the same and changing the text colour manually or using VBA shouldn't make a difference. I have tried different approaches for changing the text colour of the first n characters with VBA but always get the same (odd) result.

Is there a way to set the colour of the first n characters with VBA without getting the odd conditional formatting result? Or is this really how it should work? To me it looks like a program bug that needs to be fixed.

Anomaly with VBA and Conditional Formatting - Excel Bug?

0 Answers