Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Aug 2015
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Max/Min on Index Match Based on Color

    Hi, first time posting, so please bear with me

    I am trying to figure out how to pull Max and Min values of a corresponding range using the color index as the lookup value.

    I have a forecasting spreadsheet used to schedule employees on one or various projects at a time and helps me ensure that the employee is 100% allocated on any given day. Each chunk of allocation is assigned a different color. I was able to use VBA to pull the color index and to find out how many days the allocation lasts. Now, I need to find the start and end dates associated with that particular allocation/range. I attached a copy of a mock spreadsheet to help explain my predicament. Would greatly appreciate it if anyone could help.
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    I am trying to figure out how to pull Max and Min values of a corresponding range using the color index as the lookup value.

    Without opening a macro file from someone who is new to the forum, I am taking a shear guess on what you are trying to achieve. The following code will find the max value of from a list of values with a certain color (yellow) then index a value from another column. Hope this may point you in the right direction.

    HTH,
    Maud

    colormatch.png

    IFTM that there is a named range called Rng for the range of filled cells.
    Last edited by Maudibe; 2015-08-29 at 07:23. Reason: IFTM

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    SilliiMii (2015-08-31)

  4. #3
    New Lounger
    Join Date
    Aug 2015
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    Without opening a macro file from someone who is new to the forum, I am taking a shear guess on what you are trying to achieve. The following code will find the max value of from a list of values with a certain color (yellow) then index a value from another column. Hope this may point you in the right direction.

    HTH,
    Maud

    colormatch.png

    IFTM that there is a named range called Rng for the range of filled cells.
    Hi Maud,

    Thank you for responding and helping me with this! I apologize. I understand your skepticism in opening a file from a relative stranger

    The solution you provided is close to what I am trying to achieve. I bet that I only need to make a slight amendment to the solution you provided to get what I need, but I can't seem to figure it out. Here is an image of the table that I am working on: http://imgur.com/86oEI2T (I hope this will be a safer option for you).

    In column E, I have a block of light blue and one in dark blue. Is there a VBA function that will allow me to find the corresponding start date listed in column A? And, the corresponding end date?

    Again, thank you very much for helping me! I've been working on this for 3 months and finally feel like the solution is so close!

    Grateful,
    SilliiMii

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •