Forum Discussion

Clare McKeown's avatar
Clare McKeown
Copper Contributor
Jul 18, 2017
Solved

Conditional Formatting using both current and adjacent cells

Apologies if this is a duplicate - my previous attempt didn't seem to post properly.   Hello!   I am trying to format a column of cells based on both the number contained within the cells to be f...
Excel Desktop
Formulas & Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Jul 18, 2017

    Hi Clare,

     

    I didn't check the logic of your function, just to mention what Excel adds extra quotations since you use incorrect syntaxis for the formula, thus Excel considered it as text.

     

    AND and OR are functions, not operators. Correct syntaxis will be =AND(a,b) not =(a AND b) 

DataKnight's avatar
DataKnight
Copper Contributor
Feb 14, 2019

I have a similar issue. I have 2 columns K and N in my spreadsheet that I need to format. Column K has values which are conditionally formatted based on their value. However I'd like to add a further  conditional format based on whether Column N has a 'y' in it and colour the cell in Column K green but cant get it to work. I have this formula in the cell ....=($N1173 ="y") as an example.

My rules are attached.

 

Any help appreciated.

It's a nice to have requirement rather than a must.

 

Thanjks

Ken

DataKnight's avatar
DataKnight
Copper Contributor
Feb 14, 2019
Just got it working. It seems to need you to put the conditional formatting in the first cell of the appropriate column of the spreadsheet then it propagates down.

Am I correct?
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 15, 2019

    Yes, if you use the rule with the formula, you write the formula for the first cell of your range. It is applied to other cells of the range approximately the same way as if you write formula for the one cell and that drag it down. Other words you shall take care about absolute and relative references in your formula.

Resources