View original (French)

Cell format issue in cell Y59 on the calendar sheet

Solved
PHILOU10120 -  
Hello,

I’m looking for a solution to a formatting issue in cell Y59 on the calendar sheet. Normally the “T” (working day) should have no color, but now it has a red background with yellow text, like the “F” for holidays. I haven’t found how to restore the correct formats according to the contents of the cells.

the link to the file
https://www.transfernow.net/dl/202609249QJ06KOP

To provide this 2027 table to the regulars as every year since I’m retiring, if any evolution is missing, please let me know so I can update it.

Thank you for your help

3 answers

  1. danielc0

    Hello,

    If I understand correctly, the formula for the rule should be:

     =IF(ISERROR(VLOOKUP(C15;holiday_list;1;FALSE));0;1)

    (instead of C13)

    Daniel

    0
    1. PHILOU10120 Author Contributor

      Hello Daniel

      The cell Y59 does not contain a formula; it’s a paste-values from row 58

      The formatting of row 59 works except for cell Y59

      That’s my problem and I have no idea where it could come from, I can’t find any trace of the formatting

      Thank you for taking the time to respond

      0
    2. danielc0
      @PHILOU10120

      Hello everyone,

      @PHILOU10120 StatusContributor :

      It is the MFC formula for holidays. (I indicated "formule de la règle")

      Daniel

      0
    3. PHILOU10120 Author Contributor
      @danielc0

      Hello Daniel

      Ultimately the problem was my memory; I knew I had created formulas but I didn’t remember the MFCs, so no idea about conditional formatting

      Thanks to everyone for your help

      Philou10120

      0
  2. bigoudiii

    Hello,

    The problem probably comes from your MFCs being a bit anarchic.

    Your modified file for just the MFC holiday, to test

    0
    1. PHILOU10120 Author Contributor

      Good evening, bigoudiii

      Thank you for putting the MFC back at the top of the headers, they had been forgotten

      0
  3. blackmefias_3350

    Hello,

    I suggest trying this formula in the MFC to color the cells for holidays:

    =NB.SI($D$2:$D$32; B13) > 0

    $D$2:$D$32 = the range of cells that contains the list of holidays in your file.

    B13 = the cell of the day in your Calendar (to adapt according to your grid)

    This formula works without RECHERCHEV(), does not return an error, and easily adapts if you add or modify dates in the list.

    0
    1. PHILOU10120 Author Contributor

      Good evening blackmefias_3350

      Thank you for your reply, the file is currently in View All mode

      A macro command is used to update the calendar so there are hidden rows when it is used

      My problem is actually a memory issue; I can't remember the MFCs anymore.

      This file is over 20 years old and I’m having a bit of trouble remembering everything

      0