Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Highlight subtotals

0

In a very large spreadsheet, how do you highlight every other line?

Answer
Discuss

Answers

0
Selected Answer

A simple loop in VBA can do this (if you're sure that's what you want).

It will embolden rows 2, 4 etc. but you can change the n=2 to n=1 if you want the first line bold (since each step of two rows also sets the other row to not bold):

Private Sub BoldenEveryOtherRow()

Application.ScreenUpdating = False

For n = 2 To Range("A" & Rows.Count).End(xlUp).Row Step 2
    Rows(n).EntireRow.Font.Bold = True
    Rows(n + 1).EntireRow.Font.Bold = False
Next n

Application.ScreenUpdating = True

End Sub
Note that it assumes your data rows have something in column A (if not then change the bold A in the formula above to suit) and that ScreenUpdating is first turned off then on again for speed (since you say your file is very large).
Discuss

Discussion

Thank you John.  
R g s (rep: 2) May 5, '21 at 2:55 pm
Thanks R but if my answer solved your problem, you should mark my Answer as Selected (for me and for the guidance of others). Thanks in advance 
John_Ru (rep: 6102) May 5, '21 at 3:26 pm
Add to Discussion
0

You can use conditional formatting to achieve that.

  1. Select the entire worksheet.
  2. Conditional Formatting > New Rule > Use a formula to determine which cells to format.
  3. Enter the formula given below and set the fill colour of your choice.
=MOD(ROW(),2)
or
=MOD(ROW(),2)=0

You can use another quotient to colour every 3rd or 5th row. The modulus determines which is the first row to be coloured. If the modulus is omitted (first formula) any modulus other than 0 will be coloured so that works only if the quotient is 2.

Since your sheet is very large you can also set the format for a single cell. Then, with that cell selected, call up CF > Manage Rules and set Applies to range to include the portion of the worksheet you want formatted, usually excluding some rows at the top.

As an alternative, convert your range to a table which has the alternate row colouring by default. You can check the "Banded Rows" box to turn it on and off.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login