Forum Discussion

James Urbanski's avatar
James Urbanski
Copper Contributor
Oct 02, 2017

Conditional formatting with adjacent cells

 

Hello!

I have a spreadsheet with continously expanding rows. I want to create one column that has conditonal formatting with its corresponding adjacent cell. The format painter doesn't work because it will not allow for relative formatting, only absolute. The only solution I see is to manually enter the formula into each cell. This is not practical.

Is there and way to reuse the same formula relative to the cells being formatted?

Thank you.

excel
Formulas and Functions

10 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Oct 02, 2017

    Hi James,

     

    If the rule is the same format painter shall work. For example, that is done by format painter:

     

    • James Urbanski's avatar
      James Urbanski
      Copper Contributor
      Oct 03, 2017

      Thank you. I appreciate your attempting to help me.

      Let me better explain what I require:

       

      In cell I3, i want to use the conditional format based on the value of J3.

      I then want I4 to apply the same formula based on the value of J4. The same for I5 and J5, and so on.

      I can use the format painter to apply the formula in the coulumn "I", but it will apply the formula only based on the value of J3.

       

      I hope this provides more clairity of the situation, and I am grateful for the help. -James

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Oct 03, 2017

        Hi James,

         

        I see. The workaround is to use offset, which gives the reference on next column and current row. Not sure what's your formula, could be like

        =0.67*OFFSET($J$3,ROW()-3,0,1,1)

        and apply it to your entire range. row()-3 returns first row in your range.

Resources