Adding formatting to a row

[DaveUK]

Please can someone tell me how (in VBA) to add bold type formatting to any nonempty cell in Row 5 form column B.(Cell B5).

Many thanks

 

[tactps]

Press Alt F11
In the left pane, double-click on the sheet this is to be applied to.
Paste this into the right pane:

Private Sub Worksheet_Change(ByVal Target As Range)
[b5].Font.Bold = True
End Sub

 

[DaveUK]

Thanks for the reply!!!

However this is not really what i need as new worksheets are created automatically and during this process i want to add the code to do this at design time. If that makes sense.

 

[tactps]

OK then:

Double-click on "This Workbook" and use this:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
[b5].Font.Bold = True
End Sub

 

[HalfAce]

Hi Dave,
If you're looking to do this for row 5 from column B on, then tactps' solution can be amended a bit to this.
It covers text, numeric values and formulas, and executes upon selection of the sheet(s).

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error Resume Next
[B5:IV5].SpecialCells(xlCellTypeConstants).Font.Bold = True
[B5:IV5].SpecialCells(xlCellTypeFormulas).Font.Bold = True
End Sub

Hope it helps,
Dan

 

[DaveUK]

Sorry again but let me reiterate.

I need to apply the formatting programmaticaly. As new worksheets will be added depending on dates. Therefore when a month changes a new worksheet is created and also then i want to format the cells also.

I want to avoid manually selecting and setting the formatting manually.

Thanks anyway

 

[HalfAce]

Not sure I understand.
You want to format non empty cells in row 5 (except column A) on all sheets, including any newly created ones, as bold font?
If that's right then the code provided will do that. No need to select & manually format. The only thing that needs to be selected is the sheet one time to execute the formatting code, then it stays. (And the sheet selection can be done programatically if you won't be looking at them before printing or something like that.)

If this isn't what you want can you rephrase your request?

Dan