Improving my format-macro

A friend wrote me this kind of macro that allows me to bold every other line after asking how many rows I want to go thru. I would like to get this improved. Or whole new macro, doesn't matter.

1) Is there a way to automatize this macro to check what is the final row with entered data? So that empty rows at the end (not in middle of the sheet!) would be skipped.

2) Is there a way to improve the "auto-check" so that it would do that for every sheet on that book? There are different amount of rows in sheets, maybe just checking the maximum amount of rows in a sheet would help on it? It just takes minutes to go theu whole 65536 rows, but I have like under 2000 rows per sheet at maximum. So making a macro that goes thru first 2000 rows in every sheet would be one solution.

3) How could I add that kind of command that font thru whole sheet (or rather whole book) would be changed to Courier. So basically something like "select whole book - set font to courier". I like to work with Arial, but Courier is better when publishing and viewing with Firefox.

4) How to add that kind of command that the whole book gets grid between cells? The grid I want is "all the borders/lines". So basically something like "select whole book - set all lines/borders on". I like to work without borders/lines, but it looks better to have those lines when publishing and viewing with Firefox.

The macro I have now

Sub Lihavoi()
Rivit = CInt(InputBox("No, montaks rivii laitetaan"))
JT = False
For Each Rivi In Rows
If R < Rivit Then
If JT = False Then
Rivi.Select
Selection.Font.Bold = True
JT = True
Else
JT = False
End If
R = R + 1
Else
Exit For
End If
Next
End Sub

Some quick Finnish for you to help (having that improved code in English is absolutely more than ok, but just to help you to understand the current solution I have)
lihavoi = bold
rivit = rows
No, montaks rivii laitetaan = Well, how many rows do you want
rivi = a row

I tried this in English Excel too, doesn't work, gives the error in line

If Mod(x,2) Then

Originally Posted by Myles

My apologies. Use:

Sub mnk()


For Each sh In Worksheets
Set rng = sh.Range(sh.Range("a1"), sh.Cells(Rows.Count, "A").End(xlUp))
For Each c In rng
x = x + 1
If x Mod 2 Then
With c
.Font.Bold = True
.Font.Name = "Courier"
End With
Else
c.Font.Name = "Courier"
End If
Next
Next

End Sub

Now it works, thank you. Any idea how to add that full grid there?

Is this what you want?
Sub test()
Dim sh
Dim i As Long
For Each sh In Worksheets
'Select whole worksheet
Cells.Select
Selection.Font.Name = "Courier"
Selection.Borders.Weight = xlThick
For i = 1 To ActiveSheet.UsedRange.Rows.Count Step 2
Rows(i).Select
Selection.Font.Bold = True
Next i
Next
End Sub

Regards,

Originally Posted by TonyS

Is this what you want?
Sub test()
Dim sh
Dim i As Long
For Each sh In Worksheets
'Select whole worksheet
Cells.Select
Selection.Font.Name = "Courier"
Selection.Borders.Weight = xlThick
For i = 1 To ActiveSheet.UsedRange.Rows.Count Step 2
Rows(i).Select
Selection.Font.Bold = True
Next i
Next
End Sub

Regards,

Close. "All borders" is the option of grid I am looking for, thick is too thick.

Also, is this " 'Select whole worksheet " with purpose with that ' , does that make it as a comment?

But thanks, we are close to the solution I want now.


Also, how to make after that auto width for every column in the workbook? I mean, the same thing than selecting the whole sheet and then double-clicking the border of a column. (Though that makes it just for one sheet.)

Hi,
' makes the line as comment.
You can change the weight to xlThin or xlMedium to your liking.
Also, I've added the AutoFit line for the columns.
This procedure will apply the format to all worksheets within the workbook.

Sub test()
Dim sh
Dim i As Long
For Each sh In Worksheets
'Select whole worksheet
Cells.Select
Selection.Font.Name = "Courier"
Selection.Borders.Weight = xlThin
Selection.Columns.AutoFit
For i = 1 To ActiveSheet.UsedRange.Rows.Count Step 2
Rows(i).Select
Selection.Font.Bold = True
Next i
Next
End Sub

Originally Posted by TonyS

Hi,
' makes the line as comment.
You can change the weight to xlThin or xlMedium to your liking.
Also, I've added the AutoFit line for the columns.
This procedure will apply the format to all worksheets within the workbook.

Sub test()
Dim sh
Dim i As Long
For Each sh In Worksheets
'Select whole worksheet
Cells.Select
Selection.Font.Name = "Courier"
Selection.Borders.Weight = xlThin
Selection.Columns.AutoFit
For i = 1 To ActiveSheet.UsedRange.Rows.Count Step 2
Rows(i).Select
Selection.Font.Bold = True
Next i
Next
End Sub

I think that we are very close to the solution I want, but this macro seems to do these wanted operations only to the active sheet. Also, if it makes any difference, I am using Excel 2003.

try

Sub test()
Dim sh
Dim i As Long
For Each sh In Worksheets
sh.activate
'Select whole worksheet
Cells.Select
Selection.Font.Name = "Courier"
Selection.Borders.Weight = xlThin
Selection.Columns.AutoFit
For i = 1 To ActiveSheet.UsedRange.Rows.Count Step 2
Rows(i).Select
Selection.Font.Bold = True
Next i
Next
End Sub


the reason for this is you would need to activate the sheet.