Bolding Subtotals

[Orion Cochrane]

Excel 2003:
I was wondering if there is a way to write a macro that would bold any
subtotal on a worksheet. What I was thinking of doing is have some sort of
Do/Loop macro with a LEFT function where if Left(ActiveCell.Text, 9) =
"=SUBTOTAL" then bold the cell. It would then search all cells in the
worksheet with that condition and would look until, say, the activerow is
blank (or a better cutoff if you can think of one). I work with a lot of
reports and there are some who would like to use this macro, and I am the
only one who would know how to run it and where to install it to make it
available to others. Thanks.

 

[Bob Phillips]

Use conditional formatting with a formula to look for Total

=ISNUMBER(SEARCH("total",A2))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bernie Deitrick]

Onion,

You would want to search the formula string, not the text

Sub Macro1()

Dim myC As Range
For Each myC In Cells.SpecialCells(xlCellTypeFormulas, 23)
If InStr(1, myC.Formula, "SUBTOTAL") > 0 Then
myC.Font.Bold = True
End If
Next myC
End Sub


HTH,
Bernie
MS Excel MVP

 

[Orion Cochrane]

Thanks, Bernie. Your macro worked like a charm!
BTW, my name is Orion, not Onion. Don't worry. When I write my name, my "r"
looks like an "n" and it reads as "Onion" anyways

 

[Bernie Deitrick]

O R ion,

I'm so sorry - my eyes aren't what they used to be ;-) staring too hard at code examples....

Bernie
MS Excel MVP

 

[Orion Cochrane]

I hear you! After working on the computer all day, I find it hard to read a
book or something. Thanks again Bernie!

 

[Orion Cochrane]

I just realized I did not put a MsgBox to cancel the operation if no
subtotals were found. I tried putting an Else statement under the actions to
take if the condition was met with a MsgBox to display and the command Exit
Sub below the MsgBox, and it gave me the error "No cells were found." I gave
this macro to someone else, and I want to know where to put the MsgBox to
cancel the operation without the other person freaking out. Other than that,
the code you gave me works. Here's what I did:
Sub SubtotalBold
Dim myC As Range
For Each myC In Cells.SpecialCells(xlCellTypeFormulas, 23)
If InStr(1, myC.Formula, "SUBTOTAL") > 0 Then
myC.Font.Bold = True
Else
MsgBox [Msg]
Exit Sub
End If
Next myC
End Sub

 

[Bernie Deitrick]

Orion,

Sub Macro2()

Dim myC As Range
Dim boolFound As Boolean

boolFound = False

On Error Goto NoFormulas
For Each myC In Cells.SpecialCells(xlCellTypeFormulas, 23)
If InStr(1, myC.Formula, "SUBTOTAL") > 0 Then
myC.Font.Bold = True
boolFound = True
End If
Next myC
If boolFound Then Exit Sub
Msgbox "No SubTotal Formulas found"
NoFormulas:
End Sub

BErnie

I just realized I did not put a MsgBox to cancel the operation if no
subtotals were found. I tried putting an Else statement under the actions
to
take if the condition was met with a MsgBox to display and the command
Exit
Sub below the MsgBox, and it gave me the error "No cells were found." I
gave
this macro to someone else, and I want to know where to put the MsgBox to
cancel the operation without the other person freaking out. Other than
that,
the code you gave me works. Here's what I did:
Sub SubtotalBold
Dim myC As Range
For Each myC In Cells.SpecialCells(xlCellTypeFormulas, 23)
If InStr(1, myC.Formula, "SUBTOTAL") > 0 Then
myC.Font.Bold = True
Else
MsgBox [Msg]
Exit Sub
End If
Next myC
End Sub

 

[Orion Cochrane]

I tried that, and I get the error msgbox that you wrote when I execute the
macro when subtotals are present as well. Should the If boolFound lines be on
the top after declaring my variables? I want the macro to check for subtotals
first and, if none are found, to Exit Sub like you did. Other than that, I do
not get a debug dialog box if there are no subtotals present.
--
Please rate posts so we know when we have answered your questions. Thanks.

Orion,

Sub Macro2()

Dim myC As Range
Dim boolFound As Boolean

boolFound = False

On Error Goto NoFormulas
For Each myC In Cells.SpecialCells(xlCellTypeFormulas, 23)
If InStr(1, myC.Formula, "SUBTOTAL") > 0 Then
myC.Font.Bold = True
boolFound = True
End If
Next myC
If boolFound Then Exit Sub
Msgbox "No SubTotal Formulas found"
NoFormulas:
End Sub

BErnie

I just realized I did not put a MsgBox to cancel the operation if no
subtotals were found. I tried putting an Else statement under the actions
to
take if the condition was met with a MsgBox to display and the command
Exit
Sub below the MsgBox, and it gave me the error "No cells were found." I
gave
this macro to someone else, and I want to know where to put the MsgBox to
cancel the operation without the other person freaking out. Other than
that,
the code you gave me works. Here's what I did:
Sub SubtotalBold
Dim myC As Range
For Each myC In Cells.SpecialCells(xlCellTypeFormulas, 23)
If InStr(1, myC.Formula, "SUBTOTAL") > 0 Then
myC.Font.Bold = True
Else
MsgBox [Msg]
Exit Sub
End If
Next myC
End Sub

--
Please rate posts so we know when we have answered your questions. Thanks.

Onion,

You would want to search the formula string, not the text

Sub Macro1()

Dim myC As Range
For Each myC In Cells.SpecialCells(xlCellTypeFormulas, 23)
If InStr(1, myC.Formula, "SUBTOTAL") > 0 Then
myC.Font.Bold = True
End If
Next myC
End Sub


HTH,
Bernie
MS Excel MVP


message
Excel 2003:
I was wondering if there is a way to write a macro that would bold any
subtotal on a worksheet. What I was thinking of doing is have some sort
of
Do/Loop macro with a LEFT function where if Left(ActiveCell.Text, 9) =
"=SUBTOTAL" then bold the cell. It would then search all cells in the
worksheet with that condition and would look until, say, the activerow
is
blank (or a better cutoff if you can think of one). I work with a lot
of
reports and there are some who would like to use this macro, and I am
the
only one who would know how to run it and where to install it to make
it
available to others. Thanks.

 

[Bernie Deitrick]

You should not get the msgbox when SUBTOTAL formulas are present. But you can try this, which
covers all three cases: No formulas of any kind, formulas but no SUBTOTAL formulas, and some
SUBTOTAL formulas.


HTH,
Bernie
MS Excel MVP

Sub NewMacro()
Dim myC As Range
Dim boolFound As Boolean

boolFound = False

On Error GoTo NoFormulas
For Each myC In Cells.SpecialCells(xlCellTypeFormulas, 23)
If InStr(1, myC.Formula, "SUBTOTAL") > 0 Then
myC.Font.Bold = True
boolFound = True
End If
Next myC
If boolFound Then
MsgBox "SubTotal Formulas were found and bolded"
Else
MsgBox "No SubTotal Formulas found"
End If

Exit Sub

NoFormulas:
MsgBox "No Formulas of any kind found"

End Sub

I tried that, and I get the error msgbox that you wrote when I execute the
macro when subtotals are present as well. Should the If boolFound lines be on
the top after declaring my variables? I want the macro to check for subtotals
first and, if none are found, to Exit Sub like you did. Other than that, I do
not get a debug dialog box if there are no subtotals present.
--
Please rate posts so we know when we have answered your questions. Thanks.

Orion,

Sub Macro2()

Dim myC As Range
Dim boolFound As Boolean

boolFound = False

On Error Goto NoFormulas
For Each myC In Cells.SpecialCells(xlCellTypeFormulas, 23)
If InStr(1, myC.Formula, "SUBTOTAL") > 0 Then
myC.Font.Bold = True
boolFound = True
End If
Next myC
If boolFound Then Exit Sub
Msgbox "No SubTotal Formulas found"
NoFormulas:
End Sub

BErnie

I just realized I did not put a MsgBox to cancel the operation if no
subtotals were found. I tried putting an Else statement under the actions
to
take if the condition was met with a MsgBox to display and the command
Exit
Sub below the MsgBox, and it gave me the error "No cells were found." I
gave
this macro to someone else, and I want to know where to put the MsgBox to
cancel the operation without the other person freaking out. Other than
that,
the code you gave me works. Here's what I did:
Sub SubtotalBold
Dim myC As Range
For Each myC In Cells.SpecialCells(xlCellTypeFormulas, 23)
If InStr(1, myC.Formula, "SUBTOTAL") > 0 Then
myC.Font.Bold = True
Else
MsgBox [Msg]
Exit Sub
End If
Next myC
End Sub

--
Please rate posts so we know when we have answered your questions. Thanks.


:

Onion,

You would want to search the formula string, not the text

Sub Macro1()

Dim myC As Range
For Each myC In Cells.SpecialCells(xlCellTypeFormulas, 23)
If InStr(1, myC.Formula, "SUBTOTAL") > 0 Then
myC.Font.Bold = True
End If
Next myC
End Sub


HTH,
Bernie
MS Excel MVP


message
Excel 2003:
I was wondering if there is a way to write a macro that would bold any
subtotal on a worksheet. What I was thinking of doing is have some sort
of
Do/Loop macro with a LEFT function where if Left(ActiveCell.Text, 9) =
"=SUBTOTAL" then bold the cell. It would then search all cells in the
worksheet with that condition and would look until, say, the activerow
is
blank (or a better cutoff if you can think of one). I work with a lot
of
reports and there are some who would like to use this macro, and I am
the
only one who would know how to run it and where to install it to make
it
available to others. Thanks.

 

[Orion Cochrane]

Thanks, Bernie! It works. I realized after why you put the Exit Sub line
after the End If for the MsgBoxes and just above the NoFormulas. It works
great now. I tested it under all 3 conditions (Subtotals, No Subtotals, No
Formulas) and the appropriate actions took place. Again, you are a lifesaver
for not just me, but others who will have this macro as well.
--
Please rate posts so we know when we have answered your questions. Thanks.

You should not get the msgbox when SUBTOTAL formulas are present. But you can try this, which
covers all three cases: No formulas of any kind, formulas but no SUBTOTAL formulas, and some
SUBTOTAL formulas.


HTH,
Bernie
MS Excel MVP

Sub NewMacro()
Dim myC As Range
Dim boolFound As Boolean

boolFound = False

On Error GoTo NoFormulas
For Each myC In Cells.SpecialCells(xlCellTypeFormulas, 23)
If InStr(1, myC.Formula, "SUBTOTAL") > 0 Then
myC.Font.Bold = True
boolFound = True
End If
Next myC
If boolFound Then
MsgBox "SubTotal Formulas were found and bolded"
Else
MsgBox "No SubTotal Formulas found"
End If

Exit Sub

NoFormulas:
MsgBox "No Formulas of any kind found"

End Sub

I tried that, and I get the error msgbox that you wrote when I execute the
macro when subtotals are present as well. Should the If boolFound lines be on
the top after declaring my variables? I want the macro to check for subtotals
first and, if none are found, to Exit Sub like you did. Other than that, I do
not get a debug dialog box if there are no subtotals present.
--
Please rate posts so we know when we have answered your questions. Thanks.

Orion,

Sub Macro2()

Dim myC As Range
Dim boolFound As Boolean

boolFound = False

On Error Goto NoFormulas
For Each myC In Cells.SpecialCells(xlCellTypeFormulas, 23)
If InStr(1, myC.Formula, "SUBTOTAL") > 0 Then
myC.Font.Bold = True
boolFound = True
End If
Next myC
If boolFound Then Exit Sub
Msgbox "No SubTotal Formulas found"
NoFormulas:
End Sub

BErnie

I just realized I did not put a MsgBox to cancel the operation if no
subtotals were found. I tried putting an Else statement under the actions
to
take if the condition was met with a MsgBox to display and the command
Exit
Sub below the MsgBox, and it gave me the error "No cells were found." I
gave
this macro to someone else, and I want to know where to put the MsgBox to
cancel the operation without the other person freaking out. Other than
that,
the code you gave me works. Here's what I did:
Sub SubtotalBold
Dim myC As Range
For Each myC In Cells.SpecialCells(xlCellTypeFormulas, 23)
If InStr(1, myC.Formula, "SUBTOTAL") > 0 Then
myC.Font.Bold = True
Else
MsgBox [Msg]
Exit Sub
End If
Next myC
End Sub

--
Please rate posts so we know when we have answered your questions. Thanks.


:

Onion,

You would want to search the formula string, not the text

Sub Macro1()

Dim myC As Range
For Each myC In Cells.SpecialCells(xlCellTypeFormulas, 23)
If InStr(1, myC.Formula, "SUBTOTAL") > 0 Then
myC.Font.Bold = True
End If
Next myC
End Sub


HTH,
Bernie
MS Excel MVP


message
Excel 2003:
I was wondering if there is a way to write a macro that would bold any
subtotal on a worksheet. What I was thinking of doing is have some sort
of
Do/Loop macro with a LEFT function where if Left(ActiveCell.Text, 9) =
"=SUBTOTAL" then bold the cell. It would then search all cells in the
worksheet with that condition and would look until, say, the activerow
is
blank (or a better cutoff if you can think of one). I work with a lot
of
reports and there are some who would like to use this macro, and I am
the
only one who would know how to run it and where to install it to make
it
available to others. Thanks.