Using variable defined as As Font

[Chris Watts]

Hi,
I wish to extract all the font properties (Name, Bold, Colour etc) of a cell
(eventually I will be doing this on a character by character basis within
the cell) into a vba variable that I can manipulate within a macro. I
thought of defining the vba variable As Font, as it has all the necessary
properties, and simply doing an assignment. The code below shows my first
attempt:

Option Explicit
Sub Test()
Dim Highlighting As Font

MsgBox Cells(1, 1).Font.Name
Highlighting.Font = Cells(1, 1).Font
MsgBox Highlighting.Font.Name
End Sub

The first MsgBox tells me the font name correctly but then I get an error at
with the Highlighting.Font statement. - "Object variable or With block
variable not set
I have tried using a Set Statement and using a With Highlighting block - but
no success. Even using Highlighting.Font.Name = Cells(1, 1).Font.Name gives
the same error.

What am I doing wrong?
I am using Excel 2007 - and have a very good understanding of vba.

TIA
Chris Watts

 

[Chris Watts]

Hi,
I wish to extract all the font properties (Name, Bold, Colour etc) of a
cell (eventually I will be doing this on a character by character basis
within the cell) into a vba variable that I can manipulate within a macro.
I thought of defining the vba variable As Font, as it has all the
necessary properties, and simply doing an assignment. The code below
shows my first attempt:

Option Explicit
Sub Test()
Dim Highlighting As Font

MsgBox Cells(1, 1).Font.Name
Highlighting.Font = Cells(1, 1).Font
MsgBox Highlighting.Font.Name
End Sub

The first MsgBox tells me the font name correctly but then I get an error
at with the Highlighting.Font statement. - "Object variable or With block
variable not set
I have tried using a Set Statement and using a With Highlighting block -
but no success. Even using Highlighting.Font.Name = Cells(1, 1).Font.Name
gives the same error.

What am I doing wrong?
I am using Excel 2007 - and have a very good understanding of vba.

TIA
Chris Watts

I have found an answer, thank you, namely:

Option Explicit
Sub Test()
Dim HighLight As Font

Set HighLight = ActiveSheet.Cells(1, 1).Characters(2, 1).Font '
Works just as well on a complete cell
MsgBox HighLight.Color ' Works fine on the other font properties
End Sub

I now plan to work this up into a macro that will merge two or more cells
whilst still retaining the font characteristics of every character in both
cells.

cheers
Chris