Change Font Colour Based on Fill Colour

[Azbandar]

I have been scouring the depths of the internet for the better part of a month and I keep coming up empty for this question.

Here's what I have:

I have a sheet that I manually have to colour in my cells (based on external completion data). However, the cells need the text to be black to read it properly when no fill is created and I need to use a dark red fill when colouring a "done" item. The black font is nearly impossible to read in the dark red and I am desperately looking for anything that will help me base the font colour of my cell on the fill colour.

Current fill: None - Current font: Black, Text 1
Desired Fill: Dark Red - Desired font for this fill: White, Background 1

If I am missing something blatantly obvious, please be blunt with me! I am happy to use VBA but am still quite new to the writing of the program side in it. If this is not possible, I will just continue my manual change, it's just irritating!

Thanks in advance.

 

[Azbandar]

I found that one, ignore that last question.

 

[Fazza]

for a non-VBA solution, there is the old GET.CELL function.
one implementation could be like
create defined name CellColour with =GET.CELL(63,INDIRECT("RC",FALSE))
And then conditional formatting with formula =ABS(1-CellColour)<>1 with these formatted to white font
there are nuances, this is the basics of it

 

[Azbandar]

for a non-VBA solution, there is the old GET.CELL function.
one implementation could be like
create defined name CellColour with =GET.CELL(63,INDIRECT("RC",FALSE))
And then conditional formatting with formula =ABS(1-CellColour)<>1 with these formatted to white font
there are nuances, this is the basics of it

That works even better. For all but the Yellow, but those are only temporary and I can use an alternate shade of the yellow!

 

[Rick Rothstein]

That works even better. For all but the Yellow, but those are only temporary and I can use an alternate shade of the yellow!

Keep the Define Name (CellColor) that you already have defined, then use this Conditional Formatting formula instead of the one you have...

=ISERROR(FIND(" "&CellColor&" "," 2 4 6 8 15 19 20 24 27 28 33 34 35 36 37 38 39 40 42 43 44 45 "))

Note that you are still setting the font color to white in the Conditional Formatting dialog BUT the ColorIndex values listed above are for those whose font color should remain black. I think my selections are fine, but you can refine the above list to suit your own tastes. Note, though, that this formula won't work if the user has redefined his ColorIndex palette. That is the advantage the macro I posted earlier has over the Conditional Formatting method... the GET.CELL function used in defining the Defined Name for CellColor appears to only be able to return the ColorIndex value used to fill the cell whereas my macro works on the actual color in the cell no matter what that color is.

 

[Azbandar]

Keep the Define Name (CellColor) that you already have defined, then use this Conditional Formatting formula instead of the one you have...

=ISERROR(FIND(" "&CellColor&" "," 2 4 6 8 15 19 20 24 27 28 33 34 35 36 37 38 39 40 42 43 44 45 "))

Note that you are still setting the font color to white in the Conditional Formatting dialog BUT the ColorIndex values listed above are for those whose font color should remain black. I think my selections are fine, but you can refine the above list to suit your own tastes. Note, though, that this formula won't work if the user has redefined his ColorIndex palette. That is the advantage the macro I posted earlier has over the Conditional Formatting method... the GET.CELL function used in defining the Defined Name for CellColor appears to only be able to return the ColorIndex value used to fill the cell whereas my macro works on the actual color in the cell no matter what that color is.

Strangely enough I had to use the reverse of this to make it function and use '1 3 5' etc. Thankfully I am only using the colour index (I can switch to red vs deep red, they're only marginally different) for this one!

Thanks for all the assistance!