asked on

Excel VBA - Setting same font color like background

Dear Experts,

Actually having a specific scenario, I would need to have putting an 'x' character in all colored background cells in range A1 and G7 by VBA, but with exactly the same font color as the background found:
User generated image

There might be not only 4 colors in the scenario, so searching for a VBA logic which always putting the same color text as the background color found.

Could you please advise that which VBA code could reach that target?

In the attached Excel file filled the cells manually, if the cell has the default white background then it should stay blank without putting any text

DifferentBGColorsInRange.xlsx DifferentBGColorsInRange.xlsx

Thanks in advance,

EXPERT CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

csehz

ASKER

Martin thanks a lot it works fine.

However somehow it does not work on my real file, which has the colored cells the result of the conditional formatting.

Could you please advise whether the conditional formatted cells have some other attributes in VBA, than the ones we users manually coloring?

Bill Prew

It is true that colors applied via Conditional Formatting are not the same as when you manually format a cells Fill attribute. Conditional Formatting doesn't change the color of the cell itself, it just displays it on the screen per the conditional formatting, when the screen is rendered.

I don't think there is a way to know what color will be applied via conditional formatting in VBA, but maybe there is a trick another expert knows about...

»bp

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

csehz

ASKER

Martin thanks that second code works great on the conditional formatted cells.

Thank you just again (Bill also for you for the comment as it gave me information that the scenario with conditional formatting is technically different)

Martin Liss

You’re welcome and I’m glad I was able to help, and thank you for the testimonial.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
       Experts Exchange Most Valuable Expert (MVE) 2015, 2017
       Experts Exchange Distinguished Expert in Excel 2018
       Experts Exchange Top Expert Visual Basic Classic 2012 to 2020
       Experts Exchange Top Expert VBA 2018 to 2020

csehz

ASKER

Martin sure, already browsing through the topics there in your profile