TO DEFINE AMBIGUOUS RANGE !

[jay dean]

In Range("B2:B150") any number of cells can have their font formatted
to bold.The cells may be in succession(contiguous) or may not be in
succession(noncontiguous).

I *need a macro* that will loop through the above range, and define a
new range called myRange which is made of all the cells in
Range("B2:B200") whose font are set to bold.
(i.e) myRange=Range(__,__,__,__,__,...)
Where __,__,... are the cells whose fonts are bold.

Please I will need a macro and any help would be appreciated!
Thanks.
Jay Dean

 

[Harald Staff]

Hi Jay

A variation of John Walkenbach's code at
http://j-walk.com/ss/excel/tips/tip99.htm

Sub SelectBoldCells()
Dim WorkRange As Range
Dim FoundCells As Range
Dim Cell As Range
Set WorkRange = Range("B2:B200")
For Each Cell In WorkRange
If Cell.Font.Bold = True Then
If FoundCells Is Nothing Then
Set FoundCells = Cell
Else
Set FoundCells = Union(FoundCells, Cell)
End If
End If
Next Cell
If FoundCells Is Nothing Then
MsgBox "None."
Else
FoundCells.Select
End If
End Sub

HTH. Best wishes Harald

 

[jay dean]

Harald -
If I want to put the sum formula for FoundCells in cell A1, how would
I do that...
(i.e) Range("A1").formula="=sum(FoundCells)" ?

Thanks
Jay Dean

 

[Dave Peterson]

Maybe one of these:
Range("a1").Value = Application.Sum(FoundCells)

or
Range("a1").Formula = "=sum(" & FoundCells.Address & ")"

But be aware, =sum() has a limit of 30 arguments. If you exceed that limit,
you'll have to do a little more work.

 

[Harald Staff]

If that's what you want done -and only that- I'd rewrite it to a function:

Function SumBold(WorkRange As Range) As Double
Dim Cell As Range
Application.Volatile
On Error Resume Next
For Each Cell In WorkRange
If Cell.Font.Bold = True Then _
SumBold = SumBold + Cell.Value
Next Cell
End Function

And in a cell:
=SumBold(B2:B250)

Note that recalculation doesn'a appear whan you change format (like making a
number bold), you must press F9 to get the new result. There is no
workaround for this.

HTH. Best wishes Harald

 

[jay dean]

Excellent!
Thanks, Harald and Dave.You are the best!!
Jay Dean

 

[Harald Staff]

You're welcome Jay Dean.

Best wishes Harald