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

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




--
A36B58K641

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

Nel ,
Mr. Low ha scritto:
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

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

Hello Franz,

Your fourmula works well.

Many Thanks

Low

--
A36B58K641


"Franz Verga" wrote:

Nel ,
Mr. Low ha scritto:
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