Rowland Hamilton
Active Member
- Joined
- Nov 13, 2009
- Messages
- 250
I don't want the text to show in a visible cell B1 when a group of adjoining columns is collapsed so I want to format the font in cell b1 white when the group is collapsed (level 1) and automatic = black when it is expanded (level 2). How can I?
Here are my first two attempts to piece this together (they don't work):
And my Second attempt:
Any ideas? Thank you, Rowland
Here are my first two attempts to piece this together (they don't work):
Code:
[FONT=Times New Roman][SIZE=3]Sub Format_With_Group_Collapse()[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]'[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Dim black As Long[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Dim white As Long[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]black = vbBlack[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]white = vbWhite[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Application.ScreenUpdating = False[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Sheets("Sheet1").Select[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]With ActiveSheet[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]If ActiveSheet.Outline.ShowLevels.ColumnLevels = 1 Then[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Range("b1").Font.Color = white[/SIZE][/FONT]
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p>[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3]Else[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]If ActiveSheet.Outline.ShowLevels.ColumnLevels = 2 Then[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Range("b1").Font.Color = black[/SIZE][/FONT]
<o:p>[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3]End If[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]End With[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]End Sub[/SIZE][/FONT]
And my Second attempt:
Code:
[FONT=Times New Roman][SIZE=3]Sub Format_With_Group_Collapse()[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]'[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Application.ScreenUpdating = False[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Sheets("Sheet1").Select[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]With ActiveSheet[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]If ActiveSheet.Outline.ShowLevels.ColumnLevels = 1 Then[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Range("B1").Select[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman] With Selection.Font[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] .ColorIndex = 2[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] End With[/FONT][/SIZE]
<o:p>[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3]Else[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]If ActiveSheet.Outline.ShowLevels.ColumnLevels = 2 Then[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Range("b1").Select[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman] With Selection.Font[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] .ColorIndex = xlAutomatic[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] End With[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]End If[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]End With[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]End Sub[/SIZE][/FONT]
Any ideas? Thank you, Rowland