Posts by JCabral


    As everyone knows, the colors of the USERFORM's come in the format "&HxxBBGGRR&", ie:

    Start indicator - &

    H of Hexadecimal - H

    xx - Constant Color - 00 or 80

    Blue RGB - BB

    Green RGB - GG

    Red RGB - RR

    End indicator - &

    What I needed was, with Regular Expression - RegEx, extract from that string the substring "BBGGRR", I know it is possible to do it with the normal text functions and I know how to do it that way, but I needed it to be with RegEx.

    Basically what's failing me is defining the pattern to extract the substring.

    Thank you in advance for all the help.

    Jorge Cabral

    NOTE: I have attached a file that checks if the String format is correct, I still need to extract the Sub_String with RegEx


    As I told you, I don't give up!

    It is not because I asked for help here, or in any other forum, that I do not continue to try to solve the problem.

    I often have a solution to the problem, for which I ask for help.

    I ask for help just to see if there is a better solution or a faster solution.

    I have already managed to solve this problem in VBA, and I am trying to improve the code I have.

    Thank you for your contribution.


    Hi Carim

    Bearing in mind that we are already going, in the exchange of messages # 37, I tell you that I thought you had already realized that the UDF does not work in the way you have presented, taking into account what I need.

    I know that what I am asking for is not easy, I will not give up but, it seems that you have already decided to give up .... :(

    However, I have to thank you very much for your contribution during this long exchange of messages.

    Thank you very much


    Hi Carim

    Sorry I don't understand what's missing, in my initial example what I had was a tab (DATE) with the DATA for each task (Start Date and End Date).

    I put an OUTPUT tab to show which results I calculated manually, and so that whoever presented a solution could compare it with their own solution.

    If it is the way i want you to present the results that is failing in my real-life workbook, I added a tab where I show how the results can be presented, after studying the code I forward the results to the "PLANEAMENTO" tab myself.

    Thank you so much again


    Since you do want to specify at least the cells addresses ...

    Unfortunately ... cannot help you any further ... :(

    Hi Carim

    I'm sorry but which cell addresses do I need to specify?

    I thought I had clarified everything. :(

    I hope you continue to help in this challenge, which I know is not easy.



    Rank 1 has Start Date = ..... and End Date = .....

    Rank 2 has Start Date = ..... and End Date = .....

    Rank 3 has Start Date = ..... and End Date = .....

    These are dates that have to be calculated.

    After all periods have been calculated and ranked, these dates are the first three.

    In the example I presented in the OUTPUT tab, it corresponds to the first three rows of columns J, K, L, M and N.

    In other words, after applying the "Weight" to each task, I rank the highest to the lowest and are the first three.

    I hope I have been clear

    Yes, you are right, but I don't need them in my main layout, it is possible to create a new tab (SETTINGS) and put that data there.

    Especially because I will need them, to represent them, graphically, on lines 11, 12 and 13.

    Thanks again


    Hi Carim

    I'm sorry, but I didn't understand what you mean by "To move forward ... The Periods, corresponding to 1, 2 and 3 rank ... must be Explicit ... !!!"

    After calculating the various periods and ranking them according to the criteria I presented, it is necessary to check if each task is within the 1 or 2 or 3 rank and when placing in the N, O or P column a value other than zero, an "X" is shown or not indicating whether the task is within the period, notice that a task may be within the three ranks.

    Thank you so much again

    Hi Carim

    Let me clarify the following:

    In columns N, O and P are the results of the tasks being, or not, in the first three rank.

    The Periods, corresponding to 1, 2 and 3 rank will be represented graphically in lines 11, 12 and 13, by conditional formatting or by shapes I have not yet decided.

    Hi Carim

    First of all I apologize for my English, I may not be completely clear on what I want.

    But it is like that, for each task I only have a start date and an end date for the task. There are no more data.

    Everything in the "OUTPUT" tab was manually calculated to show what I wanted, nothing of what is there is data, it is just the results of calculations.

    In tasks I have start and end dates for tasks, and what happens is that some of these dates overlap and it is these periods that we want to know.

    I hope I have been clearer now.

    Thank you so much again

    Hi Carim

    Since is not possible edit my last post, here is an update.

    I was seeing your solution better (initial file - TestPMC_Original-V3.xlsm), and I notice that it is not calculating the periods with the highest concentration of tasks, but only the number of tasks.

    Note that the available data is only the start and end date of the tasks. What was in the OUTPUT tab was all calculated manually.

    The data are in the DATA tab, columns A, B and C, what I want is the calculations that are in OUTPUT tab, columns A to F.

    Hi again Carim

    This is the layout of my real-life workbook, I just changed the names of the tasks. As you can see, this is a Gantt layout in which I have several tasks and the Start (Column C) and End (Column E) Dates for each task.

    The data in this layout is not fixed, I repeat is not fixed and depends on the Department (Column L), that is, when I choose the department all values from Column A to Column K change., None of this is happening now, because I simplified this file as much as possible.

    The main columns for the case in question - task overlap periods - are columns N, O and P.

    Thus, 1, 2, 3 means:

    1 - first period with the highest concentration of tasks

    2 - second period of greatest concentration of tasks;

    3 - third period of greatest concentration of tasks.

    If task1 is in period 1 then column N shows a "1" if neither period 2 nor 3 appear in columns O and P then "0" appears - this has to do with conditional formatting.

    It is enough that one day of the task1 duration period, falls within any period 1, 2 or 3 to appear "1" in column N, O or P, that is, it is not necessary that the start and end date of a task is all contained in either period 1,2 or 3, one day is enough.

    This was what I needed .... not easy, I know!

    The cherry on top of the cake, was if it were possible, instead of having "1" and "0" in columns N, O and P, it was having the number of days for the duration of each task, which are in each period 1 , 2 and 3.

    I don't know if this explanation of mine is understandable - "compreensível", if you have any doubts tell me.

    Thank you so much again.