Solved

VB Macro [Excel]

Posted on 2004-04-07
270 Views
Does anyone know a small example of a script for macro'ing (VB Macro Script in Excel) that can just make every other cell bold.

A1 = Bold.False
A2 = Bold.True
A3 = Bold.False
A4 = Bold.True

Or something to that effect?

thanks
0
Question by:Narusegawa
• 4
• 3
• 2
• +1

LVL 49

Expert Comment

Something like:

Sheet1.Range("A1").Font.Bold = True
Sheet1.Range("A3").Font.Bold = True

?
0

LVL 9

Expert Comment

Something like:

Dim i as integer
Dim xlobj as Excel.Application

for i = 1 to NumberOfRows   <--- fill this in yourself
If int(i/2) <> i/2 Then
'Odd number, do nothing
Else
xlobj.Range("A" & i).Select
xlobj.Selection.Font.Bold = True
End If
Next

0

Author Comment

p_sie

That pretty much works. Would be nice to get the number of Rows Dynamically.

Dim i As Integer
Sub Macro1()
For i = 1 To 456
If Int(i / 2) <> i / 2 Then
Range("A" & i).Select
Selection.Font.Bold = True
Else
Range("A" & i).Select
Selection.Font.Size = 8
End If
Next
End Sub
0

LVL 9

Accepted Solution

p_sie earned 50 total points
You could first count them by doing

Dim j as Long

For j = 1 to 65000
If Range("A" & i).value = "" then exit for
next

For i = 1 To j
If Int(i / 2) <> i / 2 Then
Range("A" & i).Select
Selection.Font.Bold = True
Else
Range("A" & i).Select
Selection.Font.Size = 8
End If
Next
0

LVL 1

Expert Comment

TRY EITHER:---

For c = 1 To 65536
If Range("A" & c).Row Mod 2 = 1 Then
Range("A" & c).Font.Bold = True
Else
Range("A" & c).Font.Size = 8
End If
If Range("A" & c).Value <> "" Then Exit For   '>Exits if an empty cell is reached going down
Next c

OR :---

For c = 1 To 65536
If Range("A" & c).Row Mod 2 = 1 Then
Range("A" & c).Font.Bold = True
Else
Range("A" & c).Font.Size = 8
End If
If Range("A65536").End(xlUp).Row = c Then Exit For   '>Exits at last data entry in column

Next c
0

LVL 1

Expert Comment

If you just wanted the odd numbered rows to be bold, try:

For c = 1 To 65535 Step 2
Range("A" & c).Font.Bold = True
If Range("A65536").End(xlUp).Row >= c Then Exit For
Next c
0

LVL 1

Expert Comment

BTW, my code is more efficient because it doesn't select the range it is manipulating. Selecting the range wastes time, increases inefficiency, is more prone to errors e.g. someone might click the screen, and at times stalls the program if virtual memory is low.

The last post I added above is also improved (in the case of just boldening the text at every odd numbered row) because not only doesn't select the ranges it is manipulating, but it also halfs the calculations and formatting time required and therefore doubles the efficiency. It does this by simply completely ignoring even numbers (the counter doesn't even take-on an even value!).

This might see a little trite, but if you're program is large, or there are other things going on in your computer, it makes a hell of a lot of difference!

(:D)
0

LVL 9

Expert Comment

Asker was happy with one of my first answers.
0

Author Comment

Doh, thought I'd accepted that one already. Sorry
0

LVL 9

Expert Comment

Doesn't matter!
0

Join & Write a Comment Already a member? Login.

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!