Automate Macro

[jenniferspnc]

I searched this forum and found the function
=SUMPRODUCT(--(boldrange(G$4:G$19))) will count bolded words in a range of
cells. The macro it calls is at the bottom of this message.

My problem is whenever I bold or unbold a word in one of these cells the
function doesn't update unless I click in the function and hit enter. How do
I automate it to keep updating the count as I bold or unbold words?

I've been reading articles at http://www.cpearson.com/excel/Events.aspx but
must say it's a little over my head.

Oh and it may be helfpul to know that I have many sheets so I want to apply
it to the entire workbook.

Thanks for the help.

Function BoldRange(rng As Range) As Variant
'---------------------------------------------------------------------
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim iWhite As Long, iBlack As Long
Dim aryBold As Variant
If rng.Areas.Count > 1 Then
BoldRange = CVErr(xlErrValue)
Exit Function
End If
If rng.Cells.Count = 1 Then
Set BoldRange = rng
Else
aryBold = rng.Value
i = 0
For Each row In rng.Rows
i = i + 1
j = 0
For Each cell In row.Cells
j = j + 1
aryBold(i, j) = cell.Font.Bold
Next cell
Next row
End If
BoldRange = aryBold
End Function

 

[Dave Peterson]

Changing the format of a cell doesn't tell excel that it's time to recalculate.

You could make it so that your function will recalc each time excel recalcs by
adding a line to your function:


Function BoldRange(rng As Range) As Variant
application.volatile
'---------------------------------------------------------------------
Dim cell As Range, row As Range
....

But this still means that you could be one calculation behind. I wouldn't trust
the output until I forced a recalc (F9 or Shift-F9 or Ctrl-Alt-F9 or
Ctrl-Shift-Alt-F9)

See excel's help for the differences.

 

[Bob Phillips]

Basically you don't. Bolding/unbolding does not trigger a recalculation. You
could add Application Volatile to the UDF, but that will only force it to
update when something triggers a recalculation, it still won't trigger it
itself.