Posts by gijsmo

    Maybe try:

      Columns("A:A").Replace What:="Example Text", Replacement:="AS & TNT", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
        SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

    Based on the description, E7 is the date and E9 appears to be the value for a full month as you want the full value of E9 if it's the end of the month.

    If so then without C13 you can determine the fraction of the month passed using just the date value in E7 with the following:


    When the date is the end of the month, that formula will result in a value of 1.

    Then all you need to do is multiply that factor by E9:


    To get the value proportionate to the number of days in the month relative to the date in E7.

    You could extend this slightly if you want to round the resulting value to 2 decimal places with the following:


    As this question seems to come up quite often, I creted a generic file copy/merger application in Excel some time ago.

    The attached file can merge multiple Excel or CSV files into a single output Excel file.

    You can specify one of more sheets to be copied (for Excel files).

    You can also specify if you only want to copy the header from the first workbook.

    The interface should be fairly self-explanatory.

    MultiWorkbookCopy v3.xlsm

    When you select a cell on my last workbook can you see what conditional formatting is associated with the cell eg:

    And can you also confirm that this bit of code in the Worksheet_SelectionChange sub in the Existencia module is being executed when you select a new cell (put a breakpoint in the code):

    Maybe step through the DeleteTempCF code as well to see if this line is being executed:

    Other than that I remain stumped, if it is related to your language version of Excel I do not know how to fix it.

    I don't know either Bikke - it works for me 🙂

    In the Worksheet_SelectionChange sub the following line should exist:

    .FormatConditions.Add Type:=xlExpression, Formula1:="=-1"

    And it seems to be working because you are getting yellow cells.

    These yellow cells are "restored" to their original colour when the DeleteTempCF sub is called in Módulo1 by this code:

            If .FormatConditions(LoopCounter).Formula1 = "=-1" Then
            End If

    This is not language dependent.

    If both these bits of code exist, it should in theory work for you.

    I have uploaded the workbook again here for completeness.


    Hi Bikke, as per my previous message:


    ...the code would need to be changed to reflect this - the sub DeleteTempCF in Module 1 has "TRUE" in the code as does the Worksheet_SelectionChange sub in the sheet code.

    It looks like you have not modified the DeleteTempCF sub in your version.

    If I have done this correctly, the attached updated version will check the language code when you open the workbook and will modify the 2 subs for the Portuguese language (based on the this article). You may be able to use this code for other similar projects where Excel VBA is language specific.


    No problem Bikke, I had a feeling it might be language related but I wasn't sure how that would affect the code.

    I'm intrigued that the word "TRUE" has to be replaced with "VERDADEIRO" in your version of Excel.

    Glad you worked it out, obviously the code would need to be changed to reflect this - the sub DeleteTempCF in Module 1 has "TRUE" in the code as does the Worksheet_SelectionChange sub in the sheet code.

    I cannot explain that Bikke - have you moved to other cells to see if there is any difference eg, I can see:

    Maybe also try changing the value of the colour in the following line of code in the Worksheet_SelectionChange sub:

    .FormatConditions(1).Interior.Color = 13434879

    I have added cytop's example code into the previous workbook - this file is attached below.

    I have made some minor changes in line with how the original workbook was operating.

    Also, I added some code that removes all the temporary conditional formatting in the Workbook_BeforeClose sub. This means the DeleteTempCF sub has been moved to Module 1. So all temporary conditional formatting is removed before the workbook is closed (and re-saved if required).


    If it's just the same colour in each cell then you should only need to change the following code:

      lTarget.Interior.ColorIndex = 0

    The ColorIndex value would then just need to be changed to whatever that single colour is.

    It is theoretically possible and would be relatively straightforward if all the original background colours were the same.

    One way would maybe be to create a matrix of all the cell colours in the table when the workbook is opened and then use that as a reference when restoring the background colour.

    However, you also seem to have conditional formatting some of which changes the background colour and this will probably override your Worksheet_SelectionChange code ie, you won't see the colour change in the cell you select if the conditional formatting rule applies to the cell.

    Try changing the line of code from:

    arr(C, n) = Format(CDate(ar1(R, C)), "dd/mm/yyyy")


    arr(C, n) = CLng(DateSerial(Year(ar1(R, C)), Month(ar1(R, C)), Day(ar1(R, C))))

    and maybe add some formatting eg, you already have formatting for column C so add for column E as well:

        ws2.Columns("C:C").NumberFormat = "@"
        ws2.Columns("E:E").NumberFormat = "dd/mm/yyyy"

    Hi Bikke - taking a quick look at your workbook, the problem I think is being caused by the worksheet Existencia being protected.

    Your code works fine if the sheet is not protected. Assuming your sheet protection password is "blank", the following should work:

    Maybe try the following (your AppointmentWeekday function will not be required):

    Thanks Bikke, my example was wrong - I meant to say 1-May to 30-Jun.

    However, the macro will now start from the 1st day of the month specified in the Início value and finish on the last day of the month specified in the Fim value - this could be 1 month, 2 months or more, you can decide how many months to use.