Setting Range Variable
"Steve C" skrev i en meddelelse
...
Leo,
While your code does output the current range in a message box, I'd like
to
instead use a For Each statement on that range to identify cells that are
bolded, along the lines of this:
Dim cell As Range, rng As Range
Set rng = Selection
For Each cell In rng
If cell.Font.Bold = True.... etc.
My goal is to identify all the cells in a selected range that are bold and
use their addresses to build a formula farther down my spreadsheet. I
think
what's hanging me up is the syntax of the For Each line. I hope I'm
clarifying this better. Thanks for your help.
--
Steve C
"Leo Heuser" wrote:
"Steve C" skrev i en meddelelse
...
I would like to set a variable equal to whatever the currently selected
range
is on my spreadsheet. I have started my procedure as follows (assuming
that
a given range of cells has already been selected on my spreadsheet):
Dim rng as Range
Set rng = Active range of cells currently selected
What is the code I need for describing the active range? Thanks.
--
Steve C
Hi Steve
Set rng = Selection
MsgBox rng.Address
will do the job.
--
Best regards
Leo Heuser
Followup to newsgroup only please.
Steve
Here's one way:
Sub GetAddresses()
'Leo Heuser, 18 Nov. 2006
Dim Cell As Range
Dim rng As Range
Dim BoldCells As Range
Dim BoldCellsAddress As Variant
Set rng = Selection
For Each Cell In rng.Cells
If Cell.Font.Bold = True Then
If BoldCells Is Nothing Then
Set BoldCells = Cell
Else
Set BoldCells = Union(BoldCells, Cell)
End If
End If
Next Cell
If Not BoldCells Is Nothing Then
BoldCellsAddress = BoldCells.Address
MsgBox BoldCellsAddress
Else
MsgBox "No bold cells in selection."
End If
End Sub
Leo Heuser
|