Posts by Gizzmo

    Re: Macro to find criteria by a specific criteria in a given row

    Is there any reason why you can't apply a filter to your table and delete the rows based on what has been filtered.

    If you can attach a sample (refer AlanSidman as to how) then perhaps the forum could provide/suggest a suitable solution using vb or otherwise.

    Re: Project Plan with Gantt Chart and Resources Management

    Quote from Gorgey;727109

    Would it be possible to run this macro automatically at least once, e.g. at start up or while closing the file?

    Yes, I would do as per cytop reply. just add the following code should do it (see attached file)

    Private Sub Workbook_Open()
        Call UpDateGantt
    End Sub

    Quote from Gorgey;727109

    Regarding the colors, this works very well as well! The colors in the "date" table were however different to the colors in column B. I found out that replacing "Interior.ColorIndex" by "Interior.Color" ensures that colors remain unaltered. Is this the right solution?

    It works so it is a solution, as for if it is the right solution, I can't really say. Sorry I am not trying to be funny but I do not know enough about excel/vba to give an expert answer. Perhaps the 'experts' could give some advise.

    I am glad I was able to help out. I have attached revised file with some changes refer to Worksheet Change Event and additionaol named ranges etc to handle Adding Names to the Responsible List and set interior colors as well as amended formula to handle Total by resource

    Re: Getting Fill Colour value & Paste to adjacent cell

    Hi KPO967,

    Try this amended code.

    Re: Project Plan with Gantt Chart and Resources Management

    Hi Gorgey,

    Welcome to Ozgrid,

    See the attached file. I believe I have managed to answer most of your "With the help of a macro" questions which I have highlighted in Green those that I believe the macro takes care of.

    Please note the following:
    1. I have inserted named ranges Date_Current and Gantt_Range, these are used by the macro so you will need to add to your working file and adjust range accordingly.
    2. The Interior Color is set by the macro based on the Interior Color of Cells in Column B.
    3. The Macro is intended to run on Sheet Named "Sheet 1 (2)" you will need to adjust the macro if you change the sheet name.

    Hope this helps you.

    Re: Excel VBA Paste Cells into next available colum

    Hi jessica_88,

    If you really want vb solution then try this (not the greatest piece of coding but it does what you have asked for). NB:I have assumed that you are always wanting to bring back the data based on Product Number in A2 of the Maternity EPOS Data spreadsheet and paste it in the same row.

    Outside of this you could possibly use excel formula solution (vlookup, match, index etc) or Pivot table, suggest you do a search for these and have a look into.

    Re: vba to replace date in cell with "NO"

    Hi Megatronixs,

    Glad to hear it works faster.

    I have struggled to find or consider the best way to find and replace based on date format (please note I am not a programer, simply an Excel user).

    I came accross some code at [Link]…d-and-a-findall-function/[/Link] and I modified slightly!

    It uses a User Defined Function to search all cells in a range that have a particular format etc.

    I modified it to look for all cells with a Date Format "d/m/yyyy" in colmn "I". I did not put any 'speed' up coding before or after and it took approx 2 min over 1048576 rows.

    Sorry but I can do no more with my limit knowledge of vb/excel, perhaps someone else can help.

    Re: vba to replace date in cell with "NO"

    I will look into it for you but for now try this amended code. I ran this over 1048576 rows and it executed in approx 1 min!

    Re: vba to replace date in cell with "NO"

    Try this,

    Re: VBA to format cell in one sheet based on another sheet

    If you want to do this manually:

    Select Sheet 1, hold down the Ctrl+Shift keys and then hit the Page Down key until you highlight 113 sheets!

    Then on Sheet 113 select the range B6:AF29 change the colour to what you want.

    Using VBA: Try the following code

    Re: Index and Match to provide Column Heading Value

    Can you attach a sample file?