Excel VBA: function to turn activecell to bold

Question

I have the following function inside my module.

Function Colorize(myValue)
    ActiveCell.Select
    Selection.Font.Bold = True
    Colorize = myValue
End Function

The cell that will use this function should be turning bold - however, I get no error messages back and sad but true, its not turning bold. What am I missing?

Thanks

Have you run your Code in debug mode? Try `debug.print activecell.font.bold` before and after `Selection.Font.Bold = True` — DKSan, May 18 '11 at 14:10
Note that your function works as intended if called from VBA. Though not if called from a cell formula, as pointed out by @osknows. — Jean-François Corbett, May 18 '11 at 20:05
To improve performance and readability, the whole `.Select`-`Selection.` vernacular should be avoided. Just write `ActiveCell.Font.Bold = True`. Unless you specifically want to select a cell, of course... but presumably the `ActiveCell` is already selected. — Jean-François Corbett, May 18 '11 at 20:06

score 25 · Accepted Answer · 2011-05-18T14:28:39.063

25

A UDF will only return a value it won't allow you to change the properties of a cell/sheet/workbook. Move your code to a Worksheet_Change event or similar to change properties.

Eg

Private Sub worksheet_change(ByVal target As Range)
  target.Font.Bold = True
End Sub

edited May 18 '11 at 14:28

answered May 18 '11 at 14:19

I see. However, are there no individual cell events? Because in the Worksheet_Change it would affect all the ranges that are changed. – Shyam May 18 '11 at 14:25
The Excel 2007 book I am reading doesn't really clarify how to do this. – Shyam May 18 '11 at 14:25
basically what i am trying to achieve is to have a function evaluate the outcome of a range, and colorize its output - but I prefer it to be in code instead of the rules. – Shyam May 18 '11 at 14:27
1

Then use either conditional formatting, a worksheet_calculate event or include further conditions to identify which cells to make bold – May 18 '11 at 14:27
1

individual cell events can be "simulated" by If-Then-Else statements within the Change trigger: **If Target.Row=xx And Target.Column = yy Then** – MikeD May 19 '11 at 10:00

Dev.Jaap · Answer 2 · 2011-05-18T14:40:29.853

I use

            chartRange = xlWorkSheet.Rows[1];
            chartRange.Font.Bold = true;

to turn the first-row-cells-font into bold. And it works, and I am using also Excel 2007.

You can call in VBA directly

            ActiveCell.Font.Bold = True

With this code I create a timestamp in the active cell, with bold font and yellow background

           Private Sub Worksheet_SelectionChange(ByVal Target As Range)
               ActiveCell.Value = Now()
               ActiveCell.Font.Bold = True
               ActiveCell.Interior.ColorIndex = 6
           End Sub

Excel VBA: function to turn activecell to bold

2 Answers2

Linked