Set variable font in excel cell

[Les Isaacs]

Hi All

I have an access module that creates an excel file from a template and assigns various values to various cells, and it all works fine ... except that I would like to vary the font size and colour of the text in a particular cell.

So where I currently have:

range("V7").value = "NHS Pension Scheme" & vbCrLf & "Please enter all details"

I would like to have "NHS Pension Scheme" in black and size 12 but "Please enter all details" in red and size 10.

Obviously I realise that I could split the cell into two and set the font etc. in the template, but this would in fact need a lot of work as there are a lot of cell assignments, and subsequently a lot of importing from the excel file, using fixed cell addresses - which I would prefer to leave alone.

So, can I amend:
range("V7").value = "NHS Pension Scheme" & vbCrLf & "Please enter all details"
by adding fontsize and fontcolour parameters to the different text elements. Hope so

Many thanks for any help.
Les

 

[boblarson]

This is the way I find out how to code something in Excel. Just start the record macro in Excel and go select the parts you want and then do the formatting you want and then click stop on the macro, go to the VBA window and look at the code it generated. You should be able to figure out what you need from that.

 

[Trevor G]

Les you would need to look at the activecell and character size. Indicated below is an example, so a couple of withs for each part should work for you.

Sub HighlightCharacter()
With ActiveCell.Characters(Start:=3, Length:=2).Font
.FontStyle = "Bold"
.Size = 14
.ColorIndex = 3
End With
End Sub

 

[Les Isaacs]

Hi Bob and Trevor

That's really great - many thanks.

I recorded the macro as Bob suggested - which resulted in something very similar to what Trevor suggested!! So now I know the 'record and copy macro' technique and also something about setting fonts: a good day's work

Thanks again
Les

 

[Trevor G]

Happy to help and thanks for letting us know you have a solution.