Forum Discussion

Jesling's avatar
Jesling
Copper Contributor
Jan 26, 2022

Highlight FIRST EVER row ONLY

Hey guys, I have a file with column stating "Financial Free" or "Keep on Going". By usual conditional formatting, I able to highlight all the rows with "Financial Free" in green. However, I only want the first ever row which got the "Financial Free" in this case. How am I going to set the formula in conditional formatting? I have tried out COUNT but it doesn't work. 

 

Anyone can help? 

 

Thank you. 

excel
Excel on Mac
Formulas and Functions
office 365

7 Replies

Sort By
  • chahine's avatar
    chahine
    Iron Contributor
    Jan 27, 2022
    as the rows not sorted put the conditional formatting formula to be if(c4<>c5), then it will highlight wherever your row changes its name, thus first row will be highlighted everytime
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jan 26, 2022

    Hi Jesling 

     

    Alternatively and using @DTE range (C4:C16):
    - Select C4:C16
    - Home > Cond. Format. > New rule > Use a formula...

    =COUNTIF(C$4:C4,"Financial Free")=1

     

    • Jesling's avatar
      Jesling
      Copper Contributor
      Jan 27, 2022
      Hi Lorenzo, I have tried yours too. But it seems like not working still. Nothing was highlighted, I am not sure what's wrong. Is it because the column itself is with formula, not purely text. So, the conditional formatting is not working?

      Anyways, thank you so much for replying me. Hope to hear from you soon.
  • Martin_Weiss's avatar
    Martin_Weiss
    Bronze Contributor
    Jan 26, 2022

    Hi Jesling 

     

    you could use a combination of MATCH and ROW. Here is one example:

    =MATCH(C4;$C$4:$C$16;0)=ROW(C4)-3

    (depending on your local settings, you probably need to use commas instead of semicolons)

     

    The MATCH gives the position of the first occurance of every value within the list. 

    And this position is then compared with the row number. Please note, that you have to subtract the number of lines above your list.

    In my example, I have 3 lines above my list, so I subtract 3 at the end.

     

    • Jesling's avatar
      Jesling
      Copper Contributor
      Jan 27, 2022

      Martin_Weiss, I am not able to use your formula above. This is because some may get the "Financial Free" at row 10 but me may get it at row 26 instead. The column stated either "Financial Free" or "Keep On Going" is calculated by the formula. It may vary by their age, living expenses, income, returns and etc. In this case, I am not able to minus 3 as the "Financial Free" is not fix at certain row. Do we able to modify your formula above to fit the situation? 

      Anyways, thank you so much for your reply. Hope to hear from you soon. 

      • Martin_Weiss's avatar
        Martin_Weiss
        Bronze Contributor
        Jan 27, 2022

        Hi Jesling 

         

        maybe you misunderstood me, or I misunderstood you ;-).

        It was clear, that the values "Financial Free" and "Keep on Going" may occur on any given row.

        My formula takes this under consideration. The row three in my example is just the number of rows above the list, so how many rows you have before the very first entry of your list.

         

        I give you two more examples:

        My list starts at C4 with two empty records at the beginning. But still, the range starts at C4, which means there are 3 lines above my list. Therefore the 3 in my formula.

         

        And if you want to cover the complete column C, the formula would be even simpler:

        =MATCH(C1;$C:$C;0)=ROW(C1)

        It does not matter where in column C it occurs the first time.

         

        But maybe I have misunderstood your requirments.

         

         

Resources