+ Reply to Thread
Results 1 to 6 of 6

Macro to change cell text color based on darkness of cell fill color

  1. #1
    Registered User
    Join Date
    08-28-2013
    Location
    Cincinnati
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3

    Macro to change cell text color based on darkness of cell fill color

    So I have a macro that produces an excel sheet, part of which is a column of numbers, in order from least to greatest. There are anywhere from 1-500 duplicate numbers in this column before jumping up to the next, as in my example below

    271343.53
    271343.53
    271343.53
    271343.53
    271343.53
    271343.53
    271343.53
    271343.53
    271343.53
    271500
    273353
    273353
    273353
    273353
    273353
    273353
    273353
    273353
    273353
    274116.63
    274116.63
    274116.63
    274116.63
    274116.63
    274116.63

    I have a macro currently that will fill these cells in different random colors for each block of the same numbers. That macro is..

    Please Login or Register  to view this content.
    This is great and does exactly what I want, however a lot of the randomly chosen fill colors are very dark, and currently I have to go through manually and change the font color to white. I am hoping that someone can help add to this macro to have it automatically detect when a cell's fill color is past a certain shade, and change the font color to white.

    Thank you in advance and any help would be greatly appreciated!

  2. #2
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Macro to change cell text color based on darkness of cell fill color

    Hi Cincy and welcome,

    See the attached file that should do what you want. I generated a table containing all 56 background colors. The table had black fonts for one column and white fonts for another column. I manually selected which background colors needed white fonts. From the selection I created a routine that would select the font color based on the 'ColorIndex' of the background color.

    I had some problems with your find function. I'm a programmer, but not an Excel expert. I rewrote your function so it would work with your test column of numbers.

    I hope this does what you want, otherwise please let my know what changes you need, if you can't do it yourself.

    Lewis

    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    08-28-2013
    Location
    Cincinnati
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3

    Re: Macro to change cell text color based on darkness of cell fill color

    Thanks, Lewis! This works exactly as i had hoped.

    Can we change what column this runs in? If I want it to run in column B on a particular sheet vs. defaulting to A, or do you know how to make it dynamically choose a column by its title (say I have a title row, c1 says "Section") can it recognize which column has that name in the first row and execute on the remainder of that column?

    Thanks a lot for your help, I greatly appreciate it!

    Rob

  4. #4
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Macro to change cell text color based on darkness of cell fill color

    Hi Rob,

    I'm glad it works for you, and thanks for the Rep points.

    Try the attached file, which allows you to change the 'Sheet', 'Start Row' and/or 'Column' by changing the code at the top of Module Mod56 Colors:
    Please Login or Register  to view this content.
    If you want, I can change it again, to:
    a. Recognize a title header (I would need to know more information about the title header).
    b. Allow you to select a specific column using the mouse after the macro has started.

    Lewis
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-28-2013
    Location
    Cincinnati
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3

    Re: Macro to change cell text color based on darkness of cell fill color

    Hi Lewis,

    I apologize for the long delay for this response, I have been very busy lately.

    So I am a bit confused with some of your code, so maybe it will be easier if I give you a full example of all i'm trying to do. I have attached an excel sheet with a list of file path's. I take these file path's and run a macro i wrote called "DocQC", which will simply split this text out into several columns. Once you run that macro, you will see that column B is the list of numbers the way I explained in my original post. What I would like to do is incorporate your work in to this macro, so that when I run "DocQC", it will format the file paths, highlight sets of like numbers in B, and change the dark fill cells font color to white.

    I really appreciate all your help!

    Rob

    **Edit: I must have uploaded the original blank vs. the one saved as a macro enabled workbook, sorry! Try this.
    Attached Files Attached Files
    Last edited by cincyshirm61; 04-25-2014 at 03:54 PM.

  6. #6
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Macro to change cell text color based on darkness of cell fill color

    Hi Rob,

    When I download your sample file, it seems to be a blank 'Sheet1' with nothing else.

    Lewis

+ 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. change fill color of a range of cells based on color of a cell?
    By DarMelNel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-20-2014, 04:48 PM
  2. Replies: 10
    Last Post: 06-19-2013, 05:28 PM
  3. VBA / Macro help needed to change the barchart color based on Cell Color
    By krjoshi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-28-2012, 09:15 AM
  4. Replies: 0
    Last Post: 09-26-2012, 01:08 PM
  5. change text color based on adjacent cell text color
    By matthewst in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-01-2005, 03:49 PM

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