Conditional formatting

  • Thread starter Thread starter Denny Leung
  • Start date Start date
D

Denny Leung

Dear all,

I've a column of results (e.g. A2:A100) and I want to show
if any one of the results divided by 6 is less than 1, the
font colour of that result will be changed to colour red.
Is there any way to format all results in "conditional
formatting" at one time rather than to format each result
one by one?

Thanks.
 
Denny Leung said:
Dear all,

I've a column of results (e.g. A2:A100) and I want to show
if any one of the results divided by 6 is less than 1, the
font colour of that result will be changed to colour red.
Is there any way to format all results in "conditional
formatting" at one time rather than to format each result
one by one?

Thanks.
Click to expand...

Yes. Select A2:A100, with A2 as the active cell. (To do this, you can click
on A2 and drag as far as A100, or you can click A2, scroll so you can see
A100, then hold down SHIFT whilst clicking A100.) Then enter conditional
formatting, and write a formula for A2, using relative references. In your
case, this could be
=((A2/6)<1)
Select the red format as normal; job done.

The formula works just like formulas in cells, copied down to other cells in
the column. In other words, the exact formula for each cell is adjusted
automatically, because you have used relative references. You can see this
by selecting a particular cell (say A10) and going back into conditional
formatting. You will see that its CF formula is
=((A10/6)<1)
 
It is best to set up your C.F. ahead of time as shown by Anon,
but if you already
have something set up you can select the cell with the C.F.
you want and use the Format Painter to copy the C.F.,
downside is that you will also copy any other formatting of
the original not just C.F.

You can also use this technique to wipe out C.F. with the
same warning you will also copy any other formatting of the
Format Painter source..
 
Or simplify the formula: If A2/6<1, then A2<6.


Yes. Select A2:A100, with A2 as the active cell. (To do this, you can click
on A2 and drag as far as A100, or you can click A2, scroll so you can see
A100, then hold down SHIFT whilst clicking A100.) Then enter conditional
formatting, and write a formula for A2, using relative references. In your
case, this could be
=((A2/6)<1)
Select the red format as normal; job done.

The formula works just like formulas in cells, copied down to other cells in
the column. In other words, the exact formula for each cell is adjusted
automatically, because you have used relative references. You can see this
by selecting a particular cell (say A10) and going back into conditional
formatting. You will see that its CF formula is
=((A10/6)<1)
Click to expand...
 
Back
Top