+ Reply to Thread
Results 1 to 6 of 6

Custom colour code with defined names?

  1. #1
    Registered User
    Join Date
    04-16-2012
    Location
    New Zealand
    MS-Off Ver
    Excel 2019
    Posts
    4

    Question Custom colour code with defined names?

    I'm creating a calendar, in which is going to be using a series of Excel, Word and much later down the line, Publisher files. All printed to a PDF master file for mass professional printing.

    Currently, I am trying to find a way to define the colour "January" to a HEX of #C9706A, with the other 11 months having their own unique HEX colour code as well.

    Because of memory problems I face, using the built in Colour Theme Manager doesn't work, as I can set the colour to what I require, but accent 1 means nothing to me when I'm looking for March's colour and very much means I cannot use this system at all for this purpose.

    The Style Manager for Excel isn't as wide ranging as Word's (for obvious reasons), and because of this, doesn't fully seem usable.

    Is there a way to define "January" with a colour value of "#C9706A" that could be applied to either 'colourable information' (ie border, fill, highlight, font, etc) or at least make it available with the defined name visible?

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,890

    Re: Custom colour code with defined names?

    It would be possible to design something using macros. For example, the right-click menu could be updated to list the months used as color names and apply the selected color to the currently selected cells. Or a menu could come up on double-click.

    What is it that you want to apply the colors to? Cell fill? Text? How many sheets? Specific cells or any cell?

    Once the color numbers are defined, are they fixed, or will they change frequently?

    What are the memory problems?

  3. #3
    Registered User
    Join Date
    04-16-2012
    Location
    New Zealand
    MS-Off Ver
    Excel 2019
    Posts
    4

    Re: Custom colour code with defined names?

    For Excel, its currently only Borders, but will soon also be Fill. I'm still trying to work out how to make this calendar fully automatic, but I haven't researched that part of the process yet.

    The memory problems would be recalling which Default name the Colour Theme uses for which month, currently I've tried using one theme, which was a nightmare, and also tried using three themes for the months, but that means more work for me when it comes to printing. So for me, the Colour Theme Manager isn't a real solution, cos trying to remember that when I need it is a problem, as well as extra time to print the pages to pdf using different themes.

    Never used macros for Excel, didn't know there was such a thing (unless that implies VBA, which I know nothing about).

    Attached is a full copy of what I currently have at the moment. Unfortunately I haven't found a way to make the calendar fully automatic, so I have formulas in the sheet to help manually update it. I forget what the colour codes where from, but it was based on some theme that was 'popular' when I google searched it. They might be changed yearly, but for now, I will be happy to leave them as fixed.
    Attached Files Attached Files

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,890

    Re: Custom colour code with defined names?

    I will take a look and what I can do for you.

    Quote Originally Posted by AnokFero View Post
    The memory problems would be recalling which Default name the Colour Theme uses for which month
    I thought you mean computer RAM.

    Never used macros for Excel, didn't know there was such a thing (unless that implies VBA, which I know nothing about).
    Macros is a colloquial term to mean VBA in Office applications. It's a programming language based on Visual Basic but it's integrated into Office.

  5. #5
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,890

    Re: Custom colour code with defined names?

    I am trying to figure out just what it is that you want to color. Now that I see your sample, are you expecting to have 12 sheets, one for each month? And each sheet will have its own color based on the color table you showed, for fonts and borders? So there is no need to dynamically change colors as data is updated, just for initial setup, or if you change the codes?

    I see you are using merged cells, which can cause problems later, depending on what you want to do in these cells.

  6. #6
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,890

    Re: Custom colour code with defined names?

    If you need borders to change color, it will require a lot of code because of the way the borders are set up and use of merged cells. I'm not going to be able to get the time to do that for you.

    However, here is a start that takes the table of colors, and applies them to the text in the table to illustrate what colors are being used. This doesn't do error checking on the hex values in the worksheet.
    Attached Files Attached Files

+ 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. Replies: 3
    Last Post: 03-14-2017, 01:48 PM
  2. [SOLVED] Error Message on Cell References, Range Names, Defined Names, etc.
    By BLS99covert in forum Excel General
    Replies: 2
    Last Post: 12-29-2014, 05:37 PM
  3. Previously working code is now is now giving Application-defined or object-defined error
    By ready2learn in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-06-2014, 07:22 PM
  4. VBA Code to specify paper size as Custom "User Defined"
    By mdlpjr in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-09-2013, 01:50 AM
  5. Replies: 1
    Last Post: 05-14-2012, 10:14 AM
  6. VBA code to hide defined names in worksheet
    By lealea1982 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-05-2010, 06:01 AM
  7. identify defined names in VBA code
    By Mike1001 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-23-2008, 11:41 AM

Tags for this Thread

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