+ Reply to Thread
Results 1 to 5 of 5

change text color based on background color

  1. #1
    Registered User
    Join Date
    04-30-2009
    Location
    Provo, Utah
    MS-Off Ver
    Excel 2007
    Posts
    19

    change text color based on background color

    I have a report that lists information about different phases of several projects. When others make changes to the sheet, they change the font color of those changes to red. Then after our weekly meeting I go through and change all the red text to black or white (depending on the background color of the cell).
    I would like to have a macro that will do this for me as it is very time consuming.

    So I'm looking for code that will do the following:
    for the cells in columns N,M,Q,R and T,
    if the background color is RGB(208, 192, 98), then fontcolor is Black
    if the background color is RGB(255, 0, 0), then fontcolor is white,
    etc.

    I tried to patch together some code (as follows) but it wasn't working.

    Please Login or Register  to view this content.
    Thanks for your help!
    JJH

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: change text color based on background color

    Hi

    I think it has to do with the RGB setting a colorindex that is the nearest to it in the palette you are using, so the resulting color may not be the same as the one that you think you have nominated.

    This will have to be adapted for your loop but try something like

    Please Login or Register  to view this content.
    What I've done is select a cell that has no color, put in the color you have nominated, determined which colorindex that results, then return it to no color (simplistically speaking). Then you test against that resulting colorindex.

    HTH

    rylo

  3. #3
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: change text color based on background color

    Hello great.bean,

    Welcome to the Forum!

    This is an expanded version of Rylo's macro. This will start at row 5 in columns M,N,P,R, and T and change the font colors based upon the cell's color.

    NOTE: This macro will not work if the cells' color is set by Conditional Formatting!
    Please Login or Register  to view this content.
    Adding the Macro
    1. Copy the macro above pressing the keys CTRL+C
    2. Open your workbook
    3. Press the keys ALT+F11 to open the Visual Basic Editor
    4. Press the keys ALT+I to activate the Insert menu
    5. Press M to insert a Standard Module
    6. Paste the code by pressing the keys CTRL+V
    7. Make any custom changes to the macro if needed at this time.
    8. Save the Macro by pressing the keys CTRL+S
    9. Press the keys ALT+Q to exit the Editor, and return to Excel.

    To Run the Macro...
    To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  4. #4
    Registered User
    Join Date
    04-30-2009
    Location
    Provo, Utah
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: change text color based on background color

    Thank you so much. That is just what I need. Thank you especially for your explicet step by step instructions.

    One problem is that I am working in a report that my boss created, and his background colors are not in the Colorindex list.
    I think it would work to see if the background color maches a refference cell perhaps something like this:

    Please Login or Register  to view this content.
    I tried that, but it won't set that colorindex as a constant. Any thoughts?

    JJH

  5. #5
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: change text color based on background color

    Hello great.bean,

    The background colors of the cells are controlled by Excel's color palette of 56 predefined colors. Each color has a unique number. This is the ColorIndex. To say that background colors are not in the ColorIndex list, suggests to me, he may have defined some custom colors or the boss' monitor is not rendering the colors same as on your machine.

    Here is how you can retrieve the ColorIndex of the ActiveCell. Select the cell you want the ColorIndex for, and run this macro using ALT+F8. The message box will display the value.
    Please Login or Register  to view this content.
    Adding the Macro
    1. Copy the macro above pressing the keys CTRL+C
    2. Open your workbook
    3. Press the keys ALT+F11 to open the Visual Basic Editor
    4. Press the keys ALT+I to activate the Insert menu
    5. Press M to insert a Standard Module
    6. Paste the code by pressing the keys CTRL+V
    7. Make any custom changes to the macro if needed at this time.
    8. Save the Macro by pressing the keys CTRL+S
    9. Press the keys ALT+Q to exit the Editor, and return to Excel.

    To Run the Macro...
    To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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