Highlight cells based on row number on VBA

Question

I want to change the cell style based on the row number. I am still new on VBA. Here is my code:

Sub format()
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

    For i = 1 To FinalRow 
        If Rows.Count = 2 * i + 1 Then
            Selection.Style = "Good"
        ElseIf Rows.Count = 2 * i Then
            Selection.Style = "Bad" 
        End If

        ActiveCell.Offset(1, 0).Select
   Next i
End Sub

The loop moves to the next cell but does not highlight if a criteria is met. May you please help me.

Your criteria doesn't make sense. Can you explain which cells should be "good" and which "bad"? Or give a example data? — Pᴇʜ, Aug 17 '18 at 08:57
I basically want all the odd row numbers to be "good" and all the even row numbers to be "bad" irrespective of the data that is in (for e.g.) Column A. — Siyabonga Mbonambi, Aug 17 '18 at 09:06

Pᴇʜ · Answer 1 · 2018-08-17T09:13:16.780

3

I suggest the following:

Option Explicit

Public Sub FormatEvenOddRowNumbers()
    Dim FinalRow As Long
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

    Dim i As Long
    For i = 1 To FinalRow
        If i Mod 2 = 0 Then 'even row number
            Cells(i, 1).Style = "Good"
        Else 'odd row number
            Cells(i, 1).Style = "Bad"
        End If
   Next i
End Sub

To test if a row number is "even" you can use If i Mod 2 = 0 Then also you don't need to test for "odd" because if it is not "even" it must be "odd" so you can just use Else without any criteria.

Try to avoid using .Select it makes your code slow. See How to avoid using Select in Excel VBA. Instead access the cells directly like Cells(row, column).

edited Aug 17 '18 at 09:13

answered Aug 17 '18 at 09:07

Pᴇʜ

56,719
10
49
73

Sorry guys, how do I modify the existing code to take into account multiple columns too? – Siyabonga Mbonambi Aug 17 '18 at 13:12
be more clear about what you mean by multiple columns. – Pᴇʜ Aug 19 '18 at 17:10
So currently the macro runs only on a single column If I want the style on say column A and B I would have to run the macro on both columns individually. I would want to change the cell styles not just on one column at once, but on 2 or more column at once. – Siyabonga Mbonambi Aug 21 '18 at 07:23
You can either use `Range(Cells(i, 1), Cells(i, 5)).Style` to do it for column 1 to 5 or use `Rows(i).Style` to color the entire row. – Pᴇʜ Aug 21 '18 at 07:47
Thank you PEH. I appreciate it. – Siyabonga Mbonambi Aug 21 '18 at 11:55

score 0 · Accepted Answer · answered Aug 17 '18 at 09:06

0

First, I think you missused Rows.Count.
Rows.Count returns the total number of rows of your sheet. So now your criteria is only highlighting the two rows that are in the middle of the sheet.

If I assume correctly that you want to put "Good" the Rows that are even and "bad" the ones that are odds. then you should change your code to something like this:

Sub format()
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

    For i = 1 To FinalRow 
        If i/2 = int(i/2) Then
            Selection.Style = "Good"
        ElseIf (i+1)/2 = int((i+1)/2) Then
            Selection.Style = "Bad" 
        End If

        ActiveCell.Offset(1, 0).Select
   Next i
End Sub

answered Aug 17 '18 at 09:06

Pierre44

1,711
2
10
32

You don't need a second criteria if it is not even it *must* be odd, so `Else` would be enough, also using `.Select` is a very bad practice, see my answer. – Pᴇʜ Aug 17 '18 at 09:15
I agree, I just wanted to leave it as close to the code in the question as possible – Pierre44 Aug 17 '18 at 09:16
1

Also you should use `i Mod 2 = 0` to test for even instead of `int(i/2)`, because Excel has more rows than `Integer` can handle and therefore it will crash if you have more than `Integer` rows with data. If you handle row counts you must use at least `Long`. – Pᴇʜ Aug 17 '18 at 09:19
You guys are amazing. Thank you very much. I appreciate it. – Siyabonga Mbonambi Aug 17 '18 at 12:32
Sorry guys, how do I modify the existing code to take into account multiple columns too? – Siyabonga Mbonambi Aug 17 '18 at 12:56

Highlight cells based on row number on VBA

2 Answers2