Bolding Subtotals

O

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.
 
B

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)
 
B

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
 
O

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 :)
 
B

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
 
O

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! :)
 
O

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
 
B

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

Orion Cochrane said:
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
 
O

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.


Bernie Deitrick said:
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

Orion Cochrane said:
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.


Bernie Deitrick said:
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.
 
B

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


Orion Cochrane said:
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.


Bernie Deitrick said:
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

Orion Cochrane said:
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.
 
O

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.


Bernie Deitrick said:
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


Orion Cochrane said:
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.


Bernie Deitrick said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top