Forum Discussion

LazyBee's avatar
LazyBee
Copper Contributor
Nov 13, 2019

Conditional Formatting on whole column with variable reference cells

Hi, I'd like to format the colour fill of multiple cells within one particular column, when it's value is less than a cell in the row above of the next column. how should I do this please?   eg....
Column Formatting
Conditional Formatting
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 14, 2019

LazyBee 

First, in this variant we apply conditional formatting to entire column D, not to concrete cell or range.

To exclude headers we may modify rule formula as

=($D1<OFFSET($D1,-1,1))*(ROW()>5)

When it'll be like

Please check in attached file.

LazyBee's avatar
LazyBee
Copper Contributor
Nov 14, 2019

SergeiBaklanPERFECT! Thankyou so much, really appreciate the additional time spent on helping me clarify my oversight, Thanks! 😄

    • unicornsssss's avatar
      unicornsssss
      Copper Contributor
      Jun 11, 2024

      SergeiBaklan Hi Sergei, I was hoping that you could answer this question that I'm having some issues with.

      I have a column with information, and I want to make the cell a certain color if the value of the cell on the next row is equal to yes.

       

      For example:

      Column 1         Column 2

      1-Jun-2024        Yes

      5-Jun-2024         No

       

      I would like that the cell in column 1 will be green if the information in column 2 says Yes, for each respective cell/row.

       

      Thank you!

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jun 11, 2024

        unicornsssss 

        That could be like

        You need to be careful with the references and applied to range.

    • rech_yp's avatar
      rech_yp
      Copper Contributor
      Oct 11, 2021

      SergeiBaklan 

       

      HI,

       

      I also encounter the same problem, but the formula doesn't apply to the other cells. 

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Oct 24, 2021

        rech_yp 

        You have two rules:

        Second one is broken, for the first one range shall start from C2