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
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Nov 13, 2019

LazyBee 

Remove the dollar signs and then Copy, Paste Formats down the range you need the format to be applied to .

  • LazyBee's avatar
    LazyBee
    Copper Contributor
    Nov 13, 2019

    Thanks Riny_van_Eekelen 

     

    I've applied your suggestion and it has worked majoritably, however there are a few that are inaccurate, i.e. same value or higher value any ideas why this is please see attached photo:

     

     

     

    Any ideas why this is happening, please? 

    Thankyou 🙂

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Nov 13, 2019

      LazyBee 

      Since you apply CF to entire column D, your formula shall be for the first cell of the range, other words for D1, otherwise the logic will be shifted. I'd suggest

      =$D1<OFFSET($D1,-1,1)

      and

      • 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

         

Resources