Help with conditional formatting

[MikeB]

I'm trying to add a conditional format to a range of cells.
Essentially to help me make sure I fill in all the values.

My sheet has a tally of games:

Played, Win, Draw and Lose,

So I have to place two values, one for Played and then another in one
fo the (Win/Draw/Lose) columns. What I want is for the Played column
to have a red fill color until the sum of Win+Draw+Lose = Played

The I'm using the "Use a formula to apply conditional formatting"
option

My formula is =(D2+E2+F2)<>C2 and then I select the Fill Option to set
the cell color to red. But I cannot figure out how to get the formula
to apply to all the cells in the range (C2:C40). Must I make the
references absolute? I surely do not have to enter the formula 40
times in the 40 different cells?

Any help here? Thanks.

 

[Pete_UK]

You should highlight that range C2:C40, with C2 as the active cell,
before selecting conditional formatting. Then by entering your CF
formula using relative addressing (as you show), Excel will
automatically adjust the formula to suit each row. You only need to
apply it once to the range of cells.

Alternatively, you enter it once for C2 and then use the Format
Painter to apply it to C3:C40.

Hope this helps.

Pete

 

[MikeB]

You should highlight that range C2:C40, with C2 as the active cell,
before selecting conditional formatting. Then by entering your CF
formula using relative addressing (as you show), Excel will
automatically adjust the formula to suit each row. You only need to
apply it once to the range of cells.

Alternatively, you enter it once for C2 and then use the Format
Painter to apply it to C3:C40.

Hope this helps.

Pete

Thanks! Got it.

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete