J
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
=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