Change Font Colour Based on Fill Colour

A

Azbandar

New Member
Joined
Jul 10, 2017
Messages
8
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.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
Lets explain what you want like this:

If cell value = "done" interior color="Red" Font color= white
If cell value= "Yes" interior color = "White" Font color= Black

etc. etc.

Are you entering these values like "done" and "yes" or have they already been entered previously?
Now you tell us what you want like my example above

Are these values only in a certain range? Like Columns "B" and "C" or may they be any where on the sheet?
 
Last edited:
Upvote 0
After you color your cells, select the cells and then run this macro (it will work no matter what the interior color fill values are, so if you add dark blue, purple or whatever later on, the macro will still work)...
Code: 
[table="width: 500"]
[tr]
	[td]Sub MakeFontColorReadable()
  Dim Cell As Range, FontColor As Long, BackColor As Long
  For Each Cell In Selection
    BackColor = Cell.Interior.Color
    Cell.Font.Color = -vbWhite * (77 * (BackColor Mod &H100) + 151 * ((BackColor \ &H100) Mod &H100) + 28 * ((BackColor \ &H10000) Mod &H100) < 32640)
  Next
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (MakeFontColorReadable) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 
Last edited:
Upvote 0
Rick Rothstein said:
After you color your cells, select the cells and then run this macro (it will work no matter what the interior color fill values are, so if you add dark blue, purple or whatever later on, the macro will still work)...
Code: 
[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub MakeFontColorReadable()
  Dim Cell As Range, FontColor As Long, BackColor As Long
  For Each Cell In Selection
    BackColor = Cell.Interior.Color
    Cell.Font.Color = -vbWhite * (77 * (BackColor Mod &H100) + 151 * ((BackColor \ &H100) Mod &H100) + 28 * ((BackColor \ &H10000) Mod &H100) < 32640)
  Next
End Sub[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

That's does exactly what I was looking for! Is there a non-macro option, or is it only viable via a macro. I've set up recorded macros in the past, but not a written one, that's the new part for me.
 
Upvote 0
Suppose for the cells with a specific label the interior-color should be red and font-color should be white. With this scenario in mind another solution is to use conditional formatting capabilities such that cells under column A equal to "done" label format them with red interior-color and white font-color.
Code: 
=A1="Done"
 
Upvote 0
My Aswer Is This said:
Lets explain what you want like this:

If cell value = "done" interior color="Red" Font color= white
If cell value= "Yes" interior color = "White" Font color= Black

etc. etc.

Are you entering these values like "done" and "yes" or have they already been entered previously?
Now you tell us what you want like my example above

Are these values only in a certain range? Like Columns "B" and "C" or may they be any where on the sheet?
Click to expand...

The issue is that the cells have text that is different for each column and row. Not an exact quick view, but it gets the idea across:

ApparelNames (Merged header)
CoatSamanthaBrian
Shirt
Brian
ShoesSamanthaAlbert
TieAlbert

<tbody>
</tbody>

So using the above, the apparel column would never have colour, BUT when the Shirt is made for Brian, I colour it red. There are other colours involved, but are all readable with the black text. I'd have used conditional formatting with anything like you are describing (or I think if I am understanding it accurately). I hope that makes it somewhat clearer.
 
Upvote 0
Rick Rothstein said:
After you color your cells, select the cells and then run this macro (it will work no matter what the interior color fill values are, so if you add dark blue, purple or whatever later on, the macro will still work)...
Code: 
[table="width: 500"]
[tr]
	[td]Sub MakeFontColorReadable()
  Dim Cell As Range, FontColor As Long, BackColor As Long
  For Each Cell In Selection
    BackColor = Cell.Interior.Color
    Cell.Font.Color = -vbWhite * (77 * (BackColor Mod &H100) + 151 * ((BackColor \ &H100) Mod &H100) + 28 * ((BackColor \ &H10000) Mod &H100) < 32640)
  Next
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (MakeFontColorReadable) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
Click to expand...

What if i want the macro to run automatically when i colour it?
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
mod(row() change font colour as required
Replies
7
Views
240
Peter_SSs
Peter_SSs
S
  • Solved
Filling cells partially (based on percentage) and any colour of my choice
Replies
7
Views
454
Peter_SSs
Peter_SSs
A
  • Question
VBA to change cell colour based on content
Replies
3
Views
164
Snakehips
Snakehips
J
  • Solved
Program Doesn't Allow Conditional Change of Font Color Without Affecting Fill
Replies
1
Views
237
jdpro
J
S
  • Question
Need help with conditional formatting
Replies
1
Views
143
NateSC
NateSC

Forum statistics

Threads
1,214,833
Messages
6,121,862
Members
449,052
Latest member
Fuddy_Duddy

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top