Excel 2002 : How to conditional format the Sub Total Table ?

[Mr. Low]

I have the following sub total table:


Subtotal Table
A B C D
1 Area Product Date Amount
2 K252 654P 25/7/07 500
3 K252 247N 25/7/07 250
4 K252 987L 25/7/07 350
5 K252 Sub total 1100
6 P318 247N 25/7/07 470
7 P318 987L 25/7/07 380
8 P318 Sub total 850
9 F241 654P 25/7/07 220
10 F241 247N 25/7/07 350
11 F241 987L 25/7/07 260
12 F241 874F 25/7/07 380
13 P241 Sub total 1210
14 G018 663K 25/7/07 410
15 G018 664U 25/7/07 550
16 G018 141T 25/7/07 420
17 P241 Sub total 1380
GRAND TOTAL 4540


May I know how to conditional format the Sub total rows with light yellow
highlighting and red bold font ?

What formula must I key in after highlighting the entire block and clicking
at Format > Conditional Formatting ?


Thanks

Low

 

[FARAZ QURESHI]

Insert the formula for row 5 after selecting the block A55 as follows
=left($A5,9)="sub total" and select the color of choice.

Same formula for other rows by replacing $A5 with $A8, $A13 & $A17

 

[FARAZ QURESHI]

OOPS Sorry!

Replace LEFT with RIGHT in the formula

 

[Mr. Low]

Dear Sir,

May I know if I need to repeat the formula entry for each Sub Total block ?

If that is the case, It would be very troublesome when dealing with a large
worksheet with many sub totals.

Is there any single formula that would be applicable to this situation ?

The user would be required to enter that formula only once.

Thanks

Low

 

[Mr. Low]

Hello Faraz,

I try to use your formula today by highlighting the entire worksheet, it
works.

Many thanks

Low