+ Reply to Thread
Results 1 to 8 of 8

Chart columns coloring automatically based on cell font colors in a row

  1. #1
    Registered User
    Join Date
    10-07-2014
    Location
    Mures, Romania
    MS-Off Ver
    2010
    Posts
    5

    Chart columns coloring automatically based on cell font colors in a row

    I'm a rookie in VBA and I have an Excel VBA issue that is driving me crazy. I have created dynamic charts that select their data using an offset formula and a defined range. I want to color the columns of the charts according to the second row of the sheet, but the numer of columns needs to come from the data of the charts. I want to apply the same macro to all of the charts in a sheet, all sheets of the workbook, if possible, that all have the same structure, but the number of columns varies.

    I tried to write the code, but (obviously), it's not working:

    Please Login or Register  to view this content.

    Any help would be highly appreciated.

  2. #2
    Registered User
    Join Date
    10-07-2014
    Location
    Mures, Romania
    MS-Off Ver
    2010
    Posts
    5

    Re: Chart columns coloring automatically based on cell font colors in a row

    Any help out there?

  3. #3
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,411

    Re: Chart columns coloring automatically based on cell font colors in a row

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

    Help us help you by posting example workbook.

    It's not clear from you code alone what is not working for you, so you will need to explain that as well. As you appear to be using the columns.count which will tell you the number of columns
    Cheers
    Andy
    www.andypope.info

  4. #4
    Registered User
    Join Date
    10-07-2014
    Location
    Mures, Romania
    MS-Off Ver
    2010
    Posts
    5

    Re: Chart columns coloring automatically based on cell font colors in a row

    Hi Andy,

    Thanks for the heads-up, I attached a sample workbook, hopefully this will help understand what I wish to accomplish (without much success so far).

    Thank you!
    Attached Files Attached Files

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,411

    Re: Chart columns coloring automatically based on cell font colors in a row

    Using the Before sheet, as that chart is dynamic

    event code
    Please Login or Register  to view this content.
    but the recolor routine in a code module
    Please Login or Register  to view this content.
    This will colour the columns according to the font colour of the data row.

  6. #6
    Registered User
    Join Date
    10-07-2014
    Location
    Mures, Romania
    MS-Off Ver
    2010
    Posts
    5

    Re: Chart columns coloring automatically based on cell font colors in a row

    Thanks for your reply.

    From what I can see, the module still doesn't work, the column colour isn't based on the font color of the 2nd row, but the row that the chart is getting it's data from. As I understand this is because Set rColor = ActiveSheet.Range("$B$2:" & ColumnLetter & "$2") sets the range to the second row, but then Set rColor = ActiveSheet.Range(Split(Split(.Formula, ",")(2), "!")(1)) overwrites this and sets the row that the chart data can be found in.

  7. #7
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,411

    Re: Chart columns coloring automatically based on cell font colors in a row

    If the colour is from row 2 only then yes remove my definition of rColor

  8. #8
    Registered User
    Join Date
    10-07-2014
    Location
    Mures, Romania
    MS-Off Ver
    2010
    Posts
    5

    Re: Chart columns coloring automatically based on cell font colors in a row

    After removing that definition, the macro worked like a charm.

    Thank you so much for all your help!

+ 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. Changing cells and column chart colors based on cell colors
    By HDeuce in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-03-2016, 11:43 AM
  2. Automatically fill a cell based on number - Column coloring
    By carduel in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-04-2016, 03:42 PM
  3. coloring series (varies in number) with designated colors in a chart using a loop
    By aochan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-08-2016, 05:08 AM
  4. How to switch colors within one coloring theme in an Excel chart?
    By math8 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 04-15-2014, 12:36 PM
  5. Can't format font colors or cell fill-in colors
    By canoeron in forum Excel General
    Replies: 3
    Last Post: 08-22-2005, 07:05 PM
  6. [SOLVED] Coloring Cell Font based on Cell Contents
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-22-2005, 08:06 PM
  7. Replies: 1
    Last Post: 02-05-2005, 02:06 PM

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