VBA on different sheets

[zs52411940]

Hi,

I appreciate very much for the help I got from this website. Now I have another question.

I have a lot of sheets in one excel file. I want to make every first row in each sheet to be in bold font. But I just want the cells that are used to be in bold instead of the whole line. That means the blank cells should not be changed.

Anybody can help me

Many thanks

 

[mikerickson]

What code do you have so far?
What did the Macro Recorder give you?

 

[zs52411940]

What code do you have so far?
What did the Macro Recorder give you?

If I do it in one sheet, Macro Recorder is enough. But different sheet has different number of cells that are in use. So I just do not know how to do this. Sorry, I did not really code yet.

 

[Yahya]

Try This

Sub Test()
Dim ws As Worksheet, LC As Integer
For Each ws In Sheets
    LC = ws.Cells(1, Columns.Count).End(xlToLeft).Column
    ws.Range(ws.Cells(1, 1), ws.Cells(1, LC)).Font.Bold = True
Next ws
End Sub

 

[hiker95]

zs52411940,


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub BoldNonBlank()
' hiker95, 08/21/2011
' http://www.mrexcel.com/forum/showthread.php?t=573343
Dim ws As Worksheet, LC As Long, a As Long
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
  LC = ws.Cells(1, Columns.Count).End(xlToLeft).Column
  For a = LC To 1 Step -1
    If ws.Cells(1, a) <> "" Then ws.Cells(1, a).Font.Bold = True
  Next a
Next ws
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the BoldNonBlank macro.