+ Reply to Thread
Results 1 to 1 of 1

Anomaly with VBA and Conditional Formatting

  1. #1
    Registered User
    Join Date
    02-15-2021
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    1

    Anomaly with VBA and Conditional Formatting

    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 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.

    For 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?

    Thanks,
    Kevin
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Conditional formatting anomaly....
    By CDandVinyl in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-12-2019, 06:50 PM
  2. [SOLVED] INDIRECT problem for anomaly detection with conditional formatting
    By eyestorm in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-07-2014, 11:45 AM
  3. Opening xlsm files with conditional formatting opens with removed conditional formatting
    By Martijn.Steenbakker in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-07-2014, 05:38 AM
  4. IF function anomaly
    By BBS in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-07-2007, 11:51 AM
  5. [SOLVED] Printing Anomaly
    By Otto Moehrbach in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-26-2005, 11:05 AM
  6. Formatting Anomaly
    By Christopher Weaver in forum Excel General
    Replies: 2
    Last Post: 05-13-2005, 06:06 PM
  7. [SOLVED] VLOOKUP Anomaly
    By Tosca in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-08-2005, 05:08 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1