I have an issue in Excel where conditional formatting produces different results depending whether the original text colour was applied with manual editing or using VBA. I am using Excel in Office 365 on Windows 10, both fully updated as of today.
In the screenshot below (and in the attached workbook) cells A1:A6 have the conditional formatting rule=NOT(ISBLANK(B1))
that makes the text green and underlined if the cell on the same row in column B isn't blank.
2021-02-16 21_57_45 EXCEL.png
For cells A2 and A3 I changed the first three characters to red by editing manually. In A3 the first three characters remain red when conditional formatting is triggered.
For cell A4 I set the first three characters to red text using the macroSub 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.
For cell A5 I set the second and third characters to red text using the macroSub 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?
Thanks,
Kevin
Bookmarks