View original (French)

Conditional Formatting to Drag

Solved
MelMez Posted messages 14 Status Member -  
Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   -
Hello everyone,

In my table with 4 columns (A to D), I want to apply conditional formatting starting from cell D and applicable to all cells (A to D), then drag it down through all the rows of my table so that:

Row 1: if D1<0 then A1:D1 turns white
Row 2: if D2<0 then A2:D2 turns white
and so on.

How can I drag my formatting down through all my rows (a total of 866)?

Thank you for your help.

PS: I cannot provide my file because my company's proxy prevents me from doing so.

Configuration: Windows 7 / Internet Explorer 8.0

13 answers

  1.
    JSmithJ Posted messages 4403 Registration date   Status Contributor Last intervention   728
     
    I use 2 methods:

    _You drag and click on the small black square at the bottom right and then you click on the small icon that appears. "You choose to copy only the formatting"

    _Home-Conditional Formatting-Manage Rules and you change the range of application

    I hope I have helped you.
    1
  2. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Hello,

    If I understood the request correctly, simply select your entire example table A1:D100 and for conditional formatting use the formula =$A1<0
    choose the color format

    A+
    Mike-31

    A period of failure is a perfect time to sow the seeds of knowledge.
    0
  3. MelMez Posted messages 14 Status Member
     
    Re,

    Well, I expressed myself poorly. I will try to provide a clearer example:

    Col. A Col. B Col. C Col. D

    L1 10/01/2022 11.67 1096.67 23.33
    L2 11/01/2022 11.67 1108.33 11.67
    L3 12/01/2022 11.67 1120.00 0.00
    L4 01/01/2023 11.67 1131.67 -11.67
    L5 02/01/2023 11.67 1143.33 -23.33

    I want my formatting to highlight L4 (based on D4) and L5 (based on D5) in white. The other rows have amounts in Col. D greater than 0, so that’s good.
    Knowing that all data is modifiable with each update of my table.

    Is that clearer?
    0
  4. MelMez Posted messages 14 Status Member
     
    Re,

    This is what I'm trying to do like this (with my example above):

    in conditional formatting (using a formula...)
    $DL5<0 (no IF because it's included in my formatting choice)
    I choose the formatting in white
    then "applies to" I select AL5:DL5

    1. the formula doesn't work (well I don't understand anything anymore)
    2. the "applies to": automatically puts $ which I don't want
    3. when it works dragging with the cross or copying while keeping the formatting doesn't work
    0
    1. PHILOU10120 Posted messages 6463 Registration date   Status Contributor Last intervention   835
       
      I tried it without "if," it didn't work. But with the formula I gave you, it was fine.
      0
    2. MelMez Posted messages 14 Status Member
       
      Ok, I'll try right away, but what does the 1 correspond to?
      0
    3. MelMez Posted messages 14 Status Member
       
      Well, it's not working. I don't understand, I commonly use this kind of formula/formatting and now I'm completely stuck.
      So when typing >> ="IF($D131<0,1,0)" and choosing my formatting, nothing happens (Applies to D131)
      0
    4. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      Hello
      simply the formatting formula in a MFC window only accepts IF statements in very specific cases
      just enter:

      =$D131=0

      that will suffice

      best regards
      0
  6. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Re,

    if you had followed my procedure your problem would be solved, let's start again
    select all your table example A1:D5
    conditional formatting, the formula is
    =$D1<0
    choose the fill color

    for your new data, select AJ1:AL5 or AL1000 why not
    and the formula will be if the values to test are in AL and your first value in AL1
    the formula will be
    =$AL1<0

    --
    A+
    Mike-31

    A period of failure is a perfect time to sow the seeds of knowledge.
    0
    1. MelMez Posted messages 14 Status Member
       
      So if my cell range goes from A12:D131, I need to select this range and enter =$D12<0, is that right?
      0
  7. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Re,

    yes absolutely if the first cell containing the first value to be tested is D12 the formula is correct, but you need to highlight your entire table A12:D131 before entering the formula

    =$D12<0

    A+
    Mike-31

    A period of failure is a perfect time to sow the seeds of knowledge.
    0
    1. MelMez Posted messages 14 Status Member
       
      Well, I (think) I followed your procedure correctly, but the negative values are not showing up in white font.
      Is it because the values are from formulas?
      If that's the case, I'm sorry for not mentioning it at the beginning of my post.
      0
  8. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Re,

    No, the formulas have nothing to do with it unless they return a text value; in that case, they need to be modified, but for that, we would need to see a part of your anonymized file that you can attach with this link

    https://www.cjoint.com/

    --
    Cheers
    Mike-31

    A period of failure is a perfect time to sow the seeds of knowledge.
    0
    1. MelMez Posted messages 14 Status Member
       
      Unfortunately, my company's proxy does not allow it.
      0
  9. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Re,

    give an example, cell A12 what formula does it have
    B12 what formula does it have
    C12 what formula does it have
    and D12 what formula does it have

    --
    A+
    Mike-31

    A period of failure is a perfect time to sow the seeds of knowledge.
    0
  10. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Hi,

    your link is unusable, click on the link below, then on browse to select your file/on create the link, just copy the link that will be generated at the top of the assistant into a post

    https://www.cjoint.com/

    --
    See you later
    Mike-31

    A period of failure is a perfect time to sow the seeds of knowledge.
    0
    2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      Remove the quotation marks that frame your formula in the MFC.
      0
    3. MelMez Posted messages 14 Status Member
       
      That's what I do, but they reset automatically.
      0
    4. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      ...oh? see here
      https://www.cjoint.com/?DEnogazEorK
      furthermore:
      1°) are you sure about your formula in A12 that returns a date, but 0?
      2°) is there a way to set up a table that stops displaying "to the last deadline" without using a conditional formatting?
      Your option is dangerous, as the risk is to delete lines that we think are empty but are not.

      best regards
      0
  11. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Hi,

    here it is simply

    https://www.cjoint.com/?DEnoe0jfph5

    reprogram the color I put in red so you can see
    --
    A+
    Mike-31

    A period of failure is a perfect time to sow the seeds of knowledge.
    0
  12. MelMez Posted messages 14 Status Member
     
    Well, thank you. I copied the formatting from your templates because it still wasn't working.

    Vaucluse,
    1) Yes, I'm sure, but the date is not entered in the table.
    2) I'm open to the idea if I can learn and minimize risks. So, I'm going to protect the sheet to avoid any issues, and I know that my deadlines cannot go beyond line 131.
    0
  13. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Re
    here is one solution among others to adjust the schedule according to the number of years displayed in B3 of the Saisie sheet
    https://www.cjoint.com/?DEnrhwYkQVN
    The formulas remain up to row 131, and the conditional formatting is only used to shade the unused rows of the table.
    For now, the formula starting from A13 refers to the number of years x 12, but it is certainly possible to modify it to indicate a specific number of months from a cell (in case the duration is not a complete number of years)

    best regards
    --
    To err is human, to persist (in error) is diabolical
    0
  14. PHILOU10120 Posted messages 6463 Registration date   Status Contributor Last intervention   835
     
    Hello

    In conditional formatting
    Use a formula to determine which cells the format will be applied to Enter this formula
    =IF($D1<0,1,0)
    Then format
    Fill white
    Apply
    Ok
    And finally copy the cell then select all the cells and paste format

    --
    Practice makes perfect. - It's when you're up against the wall that you see the bricklayer - you always learn from your mistakes.
    -1