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
LazyBee's avatar
LazyBee
Copper Contributor
Nov 14, 2019

SergeiBaklan Hmm, this seems to have complicated things slightly. I applied the example given to cell D5 "=$D5<OFFSET($D5,-1,1)" and now the 1st cell is filled red.

 

 

 

This is understandable as E4 has letters not numbers, but it hasn't resolved the original problem. Infact it seems to have delivered a new one:

 

 

When I change the value (highlighted Yellow) to trigger the formatting it actually changes D3!?

Did I miss something?

 

 

Thanks for your help

 

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!

Resources