Macro to change a number to text
 

Hi Lori,

Al's suggestion should work to exlude the number from being included in the
SUM. Alternatively, the user could use a UDF instead of SUM:

Public Function SumIfNotBold(rng As Range) As Variant
Dim c As Range
Dim v As Variant

Application.Volatile

For Each c In rng
If Not c.Font.Bold And IsNumeric(c.Value) Then v = v + c.Value
Next c

SumIfNotBold = v
End Function

The SUM forumula can then be changed to =SumIfNotBold(<your range).

That way, if the user makes a number bold, it will not be included in the
total. This will update on each calculation of the worksheet.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Lori F. wrote:
Help, please?

I have a user who has a column of accts payable numbers
and a total at the bottom (=Sum). When a payment is made,
she wants to be able to bold a number and tell Excel not
to include it in the total at the bottom.

I've created a macro to bold the current cell and format
it as text instead of a number. The cell that is now text
should not be calculated the function, but it is. What am
I doing wrong?

Thanks!

Macro to change a number to text
 

Jake,

Another great suggestion - thanks!

Lori

-----Original Message-----
Hi Lori,

Al's suggestion should work to exlude the number from
being included in the
SUM. Alternatively, the user could use a UDF instead of
SUM:

Public Function SumIfNotBold(rng As Range) As Variant
Dim c As Range
Dim v As Variant

Application.Volatile

For Each c In rng
If Not c.Font.Bold And IsNumeric(c.Value)
Then v = v + c.Value
Next c

SumIfNotBold = v
End Function

The SUM forumula can then be changed to =SumIfNotBold
(<your range).

That way, if the user makes a number bold, it will not be
included in the
total. This will update on each calculation of the
worksheet.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address
unmonitored]


Lori F. wrote:
Help, please?

I have a user who has a column of accts payable numbers
and a total at the bottom (=Sum). When a payment is
made,
she wants to be able to bold a number and tell Excel not
to include it in the total at the bottom.

I've created a macro to bold the current cell and format
it as text instead of a number. The cell that is now
text
should not be calculated the function, but it is. What
am
I doing wrong?

Thanks!

.