How do I use a if statement to determine if a number has been formatted with
a strikethrough?

I could not locate a function that determines the formatting of a number.

=CELL("format",A1) will give the format code of cell A1
but is not much use for strikethrough
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Ken" wrote in message
...
How do I use a if statement to determine if a number has been formatted
with
a strikethrough?

I could not locate a function that determines the formatting of a number.

You need a User-Defined-Function like 'IsStrikeThru' below.
Then you can use an if statement.
Ex. If(IsStrikeThru(A5)=True,"It's a Strike!","No Strike.")

'/========================================/
Public Function IsStrikeThru(Select_Cell As Range) As Boolean
On Error Resume Next
Application.Volatile
IsStrikethrough = Select_Cell.Font.Strikethrough
End Function
'/========================================/

If you want to make the UDF available to all YOUR workbooks, put it in your
Personal.xls.

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Ken" wrote:

How do I use a if statement to determine if a number has been formatted with
a strikethrough?

I could not locate a function that determines the formatting of a number.

Thanks Gary,

Parts of the script accomplished what I was looking for.
IsStrikethrough varies from TRUE to FALSE but does not affect the results.

I am trying to:
When I strike through a number in a cell, I do not want the number to sum in
A5.
A1 = 1
A2 =2
A3=SUM(A1..A2) results 3 A3 is not affected by strikethrough

I want:
A5 = 3 when the numbers are not strikethrough
A5 = 1 when A2 is strikethrough
A5 = 2 when A1 is strikethrough

The other problem is the function does not run when the strikethrough is
added to the cell, it only runs when the number is changed.

Your thoughts!

Thanks

Ken

"Gary L Brown" wrote in message
...
You need a User-Defined-Function like 'IsStrikeThru' below.
Then you can use an if statement.
Ex. If(IsStrikeThru(A5)=True,"It's a Strike!","No Strike.")

'/========================================/
Public Function IsStrikeThru(Select_Cell As Range) As Boolean
On Error Resume Next
Application.Volatile
IsStrikethrough = Select_Cell.Font.Strikethrough
End Function
'/========================================/

If you want to make the UDF available to all YOUR workbooks, put it in
your
Personal.xls.

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was
this
Post Helpfull to you?''.


"Ken" wrote:

How do I use a if statement to determine if a number has been formatted
with
a strikethrough?

I could not locate a function that determines the formatting of a
number.

The only way that I can think of to address your first issue is with a
specialized SUM UDF such as....
'/===========================================/
Public Function SumNotStrikeThru(Select_Cells As Range) As Double
Dim rngCell As Range
Application.Volatile
On Error Resume Next
SumNotStrikeThru = 0
For Each rngCell In Select_Cells
If WorksheetFunction.IsNumber(rngCell) Then
If rngCell.Font.Strikethrough = False Then
SumNotStrikeThru = SumNotStrikeThru + rngCell
End If
End If
Next rngCell
End Function
'/===========================================/

Your second issue is one of 'when does the worksheet calculate'. I'm not
going to get complicated with Class Modules and such so I'd say your best bet
is to either hit the F9 key every so often or put some code in the specific
worksheet that you are having this issue with so that when you move your
cursor the whole worksheet calculates....
'/===========================================/
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub
'/===========================================/
HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Ken" wrote:

Thanks Gary,

Parts of the script accomplished what I was looking for.
IsStrikethrough varies from TRUE to FALSE but does not affect the results.

I am trying to:
When I strike through a number in a cell, I do not want the number to sum in
A5.
A1 = 1
A2 =2
A3=SUM(A1..A2) results 3 A3 is not affected by strikethrough

I want:
A5 = 3 when the numbers are not strikethrough
A5 = 1 when A2 is strikethrough
A5 = 2 when A1 is strikethrough

The other problem is the function does not run when the strikethrough is
added to the cell, it only runs when the number is changed.

Your thoughts!

Thanks

Ken

"Gary L Brown" wrote in message
...
You need a User-Defined-Function like 'IsStrikeThru' below.
Then you can use an if statement.
Ex. If(IsStrikeThru(A5)=True,"It's a Strike!","No Strike.")

'/========================================/
Public Function IsStrikeThru(Select_Cell As Range) As Boolean
On Error Resume Next
Application.Volatile
IsStrikethrough = Select_Cell.Font.Strikethrough
End Function
'/========================================/

If you want to make the UDF available to all YOUR workbooks, put it in
your
Personal.xls.

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was
this
Post Helpfull to you?''.


"Ken" wrote:

How do I use a if statement to determine if a number has been formatted
with
a strikethrough?

I could not locate a function that determines the formatting of a
number.

One way to accomplish the tasks is to add a button to the spreadsheet that
will take the selected cells and:
Strikethrough the numbers in the cells
Then recalculate the worksheet.

My approach is shown below. The questions:
How do I bring the selected cells identifications into the button
subroutine? #1
Can I have the subroutine strike over the numbers in the cell? #2

Private Sub CommandButton2_Click()
Select_Cells As Range ??? #1 ???
Dim mgCell As Range
Application.Volatile
On Error Resume Next
For Each mgCell In Select_Cells
If WorksheetFunction.IsNumber(mgCell) Then
mgCell.Font = mgCell.Font.Strikethrough ??? #2 ???
MsgBox (mgCell) ! Just to see what is happening, will be removed !
End If
Next mgCell
Application.Calculate
End Sub

Can I do this?

Ken

"Gary L Brown" wrote in message
...
The only way that I can think of to address your first issue is with a
specialized SUM UDF such as....
'/===========================================/
Public Function SumNotStrikeThru(Select_Cells As Range) As Double
Dim rngCell As Range
Application.Volatile
On Error Resume Next
SumNotStrikeThru = 0
For Each rngCell In Select_Cells
If WorksheetFunction.IsNumber(rngCell) Then
If rngCell.Font.Strikethrough = False Then
SumNotStrikeThru = SumNotStrikeThru + rngCell
End If
End If
Next rngCell
End Function
'/===========================================/

Your second issue is one of 'when does the worksheet calculate'. I'm not
going to get complicated with Class Modules and such so I'd say your best
bet
is to either hit the F9 key every so often or put some code in the
specific
worksheet that you are having this issue with so that when you move your
cursor the whole worksheet calculates....
'/===========================================/
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub
'/===========================================/
HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was
this
Post Helpfull to you?''.


"Ken" wrote:

Thanks Gary,

Parts of the script accomplished what I was looking for.
IsStrikethrough varies from TRUE to FALSE but does not affect the
results.

I am trying to:
When I strike through a number in a cell, I do not want the number to
sum in
A5.
A1 = 1
A2 =2
A3=SUM(A1..A2) results 3 A3 is not affected by strikethrough

I want:
A5 = 3 when the numbers are not strikethrough
A5 = 1 when A2 is strikethrough
A5 = 2 when A1 is strikethrough

The other problem is the function does not run when the strikethrough is
added to the cell, it only runs when the number is changed.

Your thoughts!

Thanks

Ken

"Gary L Brown" wrote in message
...
You need a User-Defined-Function like 'IsStrikeThru' below.
Then you can use an if statement.
Ex. If(IsStrikeThru(A5)=True,"It's a Strike!","No Strike.")

'/========================================/
Public Function IsStrikeThru(Select_Cell As Range) As Boolean
On Error Resume Next
Application.Volatile
IsStrikethrough = Select_Cell.Font.Strikethrough
End Function
'/========================================/

If you want to make the UDF available to all YOUR workbooks, put it in
your
Personal.xls.

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to
''Was
this
Post Helpfull to you?''.


"Ken" wrote:

How do I use a if statement to determine if a number has been
formatted
with
a strikethrough?

I could not locate a function that determines the formatting of a
number.

Ken,
I apologize but I'm having a very hard time understanding your 2 questions.

I think you're trying to put a strikethrough for your selected cells?
Select_Cells As Range ??? #1 ???
Should Be...
Dim Select_Cells as Range
then to make it know what cells you've already selected...
Set Select_Cells = Selection

mgCell.Font = mgCell.Font.Strikethrough
To make all selected cells have a Strikethrough it should be...
rngCell.Font.Strikethrough = True

MsgBox (mgCell) ! Just to see what is happening, will be removed !
To show a message of what cell is being affected, should be...
MsgBox rngCell.Address

That said, here are some other thoughts.
In your example of 'When I strike through a number in a cell, I do not want
the number to sum in A5':
in cell A5 put...
=SumNotStrikeThru(A1:A2)
That will give you your correct answer by ignoring cells that have
strikethroughs.
If you format another cell with a strikethrough, just hit the F9 [Calculate]
key to ensure you have the correct total.

I suggest that you put the SumNotStrikeThru function in your Personal.xls so
that it will always be available to you.

'/===========================================/
Public Function SumNotStrikeThru(Select_Cells As Range) As Double
Dim rngCell As Range
Application.Volatile
On Error Resume Next
SumNotStrikeThru = 0
For Each rngCell In Select_Cells
If WorksheetFunction.IsNumber(rngCell) Then
If rngCell.Font.Strikethrough = False Then
SumNotStrikeThru = SumNotStrikeThru + rngCell.Value
End If
End If
Next rngCell
End Function
'/===========================================/

Note about User-Defined-Function SumNotStrikeThru():
Select_Cells is the range of cells to be analyzed. This selection of
cells can either be typed in or selected using the mouse. In the above
example, A1:A2 is the Select_Cells range.
rngCell is used in the FOR loop to look at each cell in the Select_Cells
range.
IsNumber - check to see if the cell being looked at (rngCell) is a number.
If it is then go to the next line of code, otherwise ignore it because we
only want to add numbers.
rngCell.Font.Strikethrough - check to see if the Strikethrough property of
the font of the cell being looked at (rngCell) has been turned on. If it has
been, ignore it, otherwise add it to the results you already have.

f you want to contact me directly, take the '_NOSPAM_' out of my email
address below.

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Ken" wrote:

One way to accomplish the tasks is to add a button to the spreadsheet that
will take the selected cells and:
Strikethrough the numbers in the cells
Then recalculate the worksheet.

My approach is shown below. The questions:
How do I bring the selected cells identifications into the button
subroutine? #1
Can I have the subroutine strike over the numbers in the cell? #2

Private Sub CommandButton2_Click()
Select_Cells As Range ??? #1 ???
Dim mgCell As Range
Application.Volatile
On Error Resume Next
For Each mgCell In Select_Cells
If WorksheetFunction.IsNumber(mgCell) Then
mgCell.Font = mgCell.Font.Strikethrough ??? #2 ???
MsgBox (mgCell) ! Just to see what is happening, will be removed !
End If
Next mgCell
Application.Calculate
End Sub

Can I do this?

Ken

"Gary L Brown" wrote in message
...
The only way that I can think of to address your first issue is with a
specialized SUM UDF such as....
'/===========================================/
Public Function SumNotStrikeThru(Select_Cells As Range) As Double
Dim rngCell As Range
Application.Volatile
On Error Resume Next
SumNotStrikeThru = 0
For Each rngCell In Select_Cells
If WorksheetFunction.IsNumber(rngCell) Then
If rngCell.Font.Strikethrough = False Then
SumNotStrikeThru = SumNotStrikeThru + rngCell
End If
End If
Next rngCell
End Function
'/===========================================/

Your second issue is one of 'when does the worksheet calculate'. I'm not
going to get complicated with Class Modules and such so I'd say your best
bet
is to either hit the F9 key every so often or put some code in the
specific
worksheet that you are having this issue with so that when you move your
cursor the whole worksheet calculates....
'/===========================================/
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub
'/===========================================/
HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was
this
Post Helpfull to you?''.


"Ken" wrote:

Thanks Gary,

Parts of the script accomplished what I was looking for.
IsStrikethrough varies from TRUE to FALSE but does not affect the
results.

I am trying to:
When I strike through a number in a cell, I do not want the number to
sum in
A5.
A1 = 1
A2 =2
A3=SUM(A1..A2) results 3 A3 is not affected by strikethrough

I want:
A5 = 3 when the numbers are not strikethrough
A5 = 1 when A2 is strikethrough
A5 = 2 when A1 is strikethrough

The other problem is the function does not run when the strikethrough is
added to the cell, it only runs when the number is changed.

Your thoughts!

Thanks

Ken

"Gary L Brown" wrote in message
...
You need a User-Defined-Function like 'IsStrikeThru' below.
Then you can use an if statement.
Ex. If(IsStrikeThru(A5)=True,"It's a Strike!","No Strike.")

'/========================================/
Public Function IsStrikeThru(Select_Cell As Range) As Boolean
On Error Resume Next
Application.Volatile
IsStrikethrough = Select_Cell.Font.Strikethrough
End Function
'/========================================/

If you want to make the UDF available to all YOUR workbooks, put it in
your
Personal.xls.

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to
''Was
this
Post Helpfull to you?''.


"Ken" wrote:

How do I use a if statement to determine if a number has been
formatted
with
a strikethrough?

I could not locate a function that determines the formatting of a
number.

Thank you, Gary.

The changes worked and the script does exactly what I wanted it to do.

I did save the functions in a personal file for future use. Great idea for
future reference.

Have a good weekend.

Ken


"Gary L Brown" wrote in message
...
Ken,
I apologize but I'm having a very hard time understanding your 2
questions.

I think you're trying to put a strikethrough for your selected cells?
Select_Cells As Range ??? #1 ???
Should Be...
Dim Select_Cells as Range
then to make it know what cells you've already selected...
Set Select_Cells = Selection

mgCell.Font = mgCell.Font.Strikethrough
To make all selected cells have a Strikethrough it should be...
rngCell.Font.Strikethrough = True

MsgBox (mgCell) ! Just to see what is happening, will be removed
!
To show a message of what cell is being affected, should be...
MsgBox rngCell.Address

That said, here are some other thoughts.
In your example of 'When I strike through a number in a cell, I do not
want
the number to sum in A5':
in cell A5 put...
=SumNotStrikeThru(A1:A2)
That will give you your correct answer by ignoring cells that have
strikethroughs.
If you format another cell with a strikethrough, just hit the F9
[Calculate]
key to ensure you have the correct total.

I suggest that you put the SumNotStrikeThru function in your Personal.xls
so
that it will always be available to you.

'/===========================================/
Public Function SumNotStrikeThru(Select_Cells As Range) As Double
Dim rngCell As Range
Application.Volatile
On Error Resume Next
SumNotStrikeThru = 0
For Each rngCell In Select_Cells
If WorksheetFunction.IsNumber(rngCell) Then
If rngCell.Font.Strikethrough = False Then
SumNotStrikeThru = SumNotStrikeThru + rngCell.Value
End If
End If
Next rngCell
End Function
'/===========================================/

Note about User-Defined-Function SumNotStrikeThru():
Select_Cells is the range of cells to be analyzed. This selection of
cells can either be typed in or selected using the mouse. In the above
example, A1:A2 is the Select_Cells range.
rngCell is used in the FOR loop to look at each cell in the Select_Cells
range.
IsNumber - check to see if the cell being looked at (rngCell) is a
number.
If it is then go to the next line of code, otherwise ignore it because we
only want to add numbers.
rngCell.Font.Strikethrough - check to see if the Strikethrough property
of
the font of the cell being looked at (rngCell) has been turned on. If it
has
been, ignore it, otherwise add it to the results you already have.

f you want to contact me directly, take the '_NOSPAM_' out of my email
address below.

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was
this
Post Helpfull to you?''.


"Ken" wrote:

One way to accomplish the tasks is to add a button to the spreadsheet
that
will take the selected cells and:
Strikethrough the numbers in the cells
Then recalculate the worksheet.

My approach is shown below. The questions:
How do I bring the selected cells identifications into the button
subroutine? #1
Can I have the subroutine strike over the numbers in the cell? #2

Private Sub CommandButton2_Click()
Select_Cells As Range ??? #1 ???
Dim mgCell As Range
Application.Volatile
On Error Resume Next
For Each mgCell In Select_Cells
If WorksheetFunction.IsNumber(mgCell) Then
mgCell.Font = mgCell.Font.Strikethrough ??? #2 ???
MsgBox (mgCell) ! Just to see what is happening, will be removed
!
End If
Next mgCell
Application.Calculate
End Sub

Can I do this?

Ken

"Gary L Brown" wrote in message
...
The only way that I can think of to address your first issue is with a
specialized SUM UDF such as....
'/===========================================/
Public Function SumNotStrikeThru(Select_Cells As Range) As Double
Dim rngCell As Range
Application.Volatile
On Error Resume Next
SumNotStrikeThru = 0
For Each rngCell In Select_Cells
If WorksheetFunction.IsNumber(rngCell) Then
If rngCell.Font.Strikethrough = False Then
SumNotStrikeThru = SumNotStrikeThru + rngCell
End If
End If
Next rngCell
End Function
'/===========================================/

Your second issue is one of 'when does the worksheet calculate'. I'm
not
going to get complicated with Class Modules and such so I'd say your
best
bet
is to either hit the F9 key every so often or put some code in the
specific
worksheet that you are having this issue with so that when you move
your
cursor the whole worksheet calculates....
'/===========================================/
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub
'/===========================================/
HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to
''Was
this
Post Helpfull to you?''.


"Ken" wrote:

Thanks Gary,

Parts of the script accomplished what I was looking for.
IsStrikethrough varies from TRUE to FALSE but does not affect the
results.

I am trying to:
When I strike through a number in a cell, I do not want the number
to
sum in
A5.
A1 = 1
A2 =2
A3=SUM(A1..A2) results 3 A3 is not affected by strikethrough

I want:
A5 = 3 when the numbers are not strikethrough
A5 = 1 when A2 is strikethrough
A5 = 2 when A1 is strikethrough

The other problem is the function does not run when the
strikethrough is
added to the cell, it only runs when the number is changed.

Your thoughts!

Thanks

Ken

"Gary L Brown" wrote in message
...
You need a User-Defined-Function like 'IsStrikeThru' below.
Then you can use an if statement.
Ex. If(IsStrikeThru(A5)=True,"It's a Strike!","No Strike.")

'/========================================/
Public Function IsStrikeThru(Select_Cell As Range) As Boolean
On Error Resume Next
Application.Volatile
IsStrikethrough = Select_Cell.Font.Strikethrough
End Function
'/========================================/

If you want to make the UDF available to all YOUR workbooks, put
it in
your
Personal.xls.

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to
''Was
this
Post Helpfull to you?''.


"Ken" wrote:

How do I use a if statement to determine if a number has been
formatted
with
a strikethrough?

I could not locate a function that determines the formatting of
a
number.