Forum Discussion

MiniAbers1's avatar
MiniAbers1
Copper Contributor
Jun 27, 2025

Colour conditional formatting

Hi, I have a formula for colour conditional formatting where it turns cell M2 orange if it has ‘Complex’ written in it but there’s nothing in N2. If N2 has a value in it then M2 goes back to its original colour (yellow).

=AND(M2=”Complex”,IFBLANK(N2))


How do I add this to all other cells in column M without doing a new rule for each cell? I need the cells to correspond- eg M3 and N3 etc

 

thanks 

excel
Formulas and Functions

1 Reply

  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Jun 27, 2025

    All you need to do is have the Applies To range cover those other cells

    e.g. Applies To: M2:M20

    If you want it to apply to cell in column N then you need to 'fix' the columna in the formula:

    =AND($M2=”Complex”,IFBLANK($N2))

    and then the Applies to could be M2:N20 

    the rule is that the formula must apply to the upper left most cell in the applies to range and then excell will apply that formula to all the other cells in the applies to range and adjust the 'relative' references (i.e. no preceding $) to each corresponding cell

Resources