Excel 2002: How to conditional format a row of cells ?

[Guest]

Dear Sir,

I have the following table to be formatted :

A B C
1 Product Invoice Amount
2 L249 2415 240
3 L249 2416 360
4 L249 2417 210
5 Subtotal L249 810
6 H741 2419 330
7 H741 2420 420
8 Subtotal H741 750
9 K786 2422 280
10 K786 2423 350
11 Subtotal K786 630
12 M844 2425 210
13 M844 2426 320
14 Subtotal M844 530
15 Grand Total 2720


I would like to color shade all the subtotal rows by using the formula at
Format > Conditional Formatting > Use Formula to format.

Kindly let me know the formula to input if the selection of the row is based
on:

1. The row in which the word Subtotal arise.
2. The row where there is a formula cell (C5, C8, C11, C14 and C15 are
formula cells

Is there a formula I can input to enable an empty row be inserted below the
subtotal row ?

Thanks

Low

 

[Franz Verga]

Nel post:[email protected],

Dear Sir,

I have the following table to be formatted :

A B C
1 Product Invoice Amount
2 L249 2415 240
3 L249 2416 360
4 L249 2417 210
5 Subtotal L249 810
6 H741 2419 330
7 H741 2420 420
8 Subtotal H741 750
9 K786 2422 280
10 K786 2423 350
11 Subtotal K786 630
12 M844 2425 210
13 M844 2426 320
14 Subtotal M844 530
15 Grand Total 2720


I would like to color shade all the subtotal rows by using the
formula at Format > Conditional Formatting > Use Formula to format.

Kindly let me know the formula to input if the selection of the row
is based on:

1. The row in which the word Subtotal arise.
2. The row where there is a formula cell (C5, C8, C11, C14 and C15
are formula cells

Is there a formula I can input to enable an empty row be inserted
below the subtotal row ?

Thanks

Low

Hi Low,

first of all you have to select your range, say A2:C15, as from your example
the active cell should be A2 in order the conditional formatting work), then
from menu Format, Conditional formatting, select Formula is and then input
this formula:

=LEFT($A2,7)="Subtotal"

then choose the format you like.


--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.


Thanks in advance for your feedback.


Ciao


Franz Verga from Italy

 

[Guest]

Hello Franz,

Your fourmula works well.

Many Thanks

Low