Dynamic Formatting with Grouping

[AllisterB]

I have a total cell that is formatted as bold. When I use grouping and hide the rows the total is summing I want the Total to be unbold.

Please see attached.

I thought I might be able to use Conditional Formatting but did not know how to reference the Grouping level in the Conditional Formatting formula.

I would appreciate any help you may have.

Allister

Book2.xlsx
	D	E	F	G
7	Before
8
9			apple	10
10			Pear	20
11			Orange	30
12			banana	40
13
14				100
15
16
17	After
18
24				100
25
Sheet1

Cell Formulas
Range	Formula
G14,G24	G14	=SUBTOTAL(9,G9:G13)

 

[Peter_SSs]

See if this would be sufficient.
Remove the bold from G14
Apply this Conditional Formatting to G14, with Bold Font as the CF formatting.

It would fail if the total of G9:G13 is in fact 0. If that could be a problem for you post back for a work-around.

20 08 10.xlsm
	G
9	10
10	20
11	30
12	40
13
14	100
Bold or Not

Cell Formulas
Range	Formula
G14	G14	=SUBTOTAL(9,G9:G13)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G14	Expression	=SUBTOTAL(9,G9:G13)=SUBTOTAL(109,G9:G13)	text	NO

 

[Peter_SSs]

a work-around.

I'll post it anyway in case it suits you better.

• In any vacant column (I used column H) place a 1 (or any number) in one/all/some of the cells in that column that would be hidden by the grouping button.
• Hide that column manually (I've left is visible for you to see filled)
• Change the CF rule to this

20 08 10.xlsm
	G	H
8
9	0	1
10	0	1
11	0	1
12	0	1
13		1
14	0
15
Bold or Not (2)

Cell Formulas
Range	Formula
G14	G14	=SUBTOTAL(9,G9:G13)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G14	Expression	=SUBTOTAL(102,H9:H13)	text	NO

.. and when the rows are hidden by the grouping button:

20 08 10.xlsm
	G	H
8
14	0
15
Bold or Not (2)

Cell Formulas
Range	Formula
G14	G14	=SUBTOTAL(9,G9:G13)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G14	Expression	=SUBTOTAL(102,H9:H13)	text	NO