Excel VBA - Loop needed for a column

Question

I'm very new to excel VBA and I can't figure out how to get this to work. I have a column (column K), with a header in K1. I get this spreadsheet every day and it has a different number of rows. Column K has numbers from 0-100. I need to highlight certain rows certain colors depending on the value in column K. This is what I have so far, but it just goes all the way down and makes every column red font. I need it to loop through k2 to the last K cell with a value and change the font color of each row.

Columns("K").Select
Dim firstCell As Integer
Dim finalCell As Integer
firstCell = Range("K2")
finalCell = Range("K65536").End(xlUp).Row
For i = firstCell To finalCell

If i > 5 Then
    Rows(i).Select
    With Selection.Font
        .Color = RGB(255, 0, 0)
    End With
ElseIf i = 4 Then
    Rows(i).Select
    With Selection.Font
        .Color = RGB(226, 107, 10)
    End With
ElseIf i = 3 Then
    Rows(i).Select
    With Selection.Font
        .Color = RGB(0, 176, 80)
    End With
ElseIf i = 2 Then
    Rows(i).Select
    With Selection.Font
        .Color = RGB(0, 112, 192)
    End With
ElseIf i = 1 Then
    Rows(i).Select
    With Selection.Font
        .Color = RGB(112, 48, 160)
    End With
End If
Next i

Two Links to get you started. [FIRST](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) and [SECOND](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Siddharth Rout, Jan 31 '14 at 21:17
BTW you can also use Autofilter to color the cells. You don't need to loop through all 100 or so cells — Siddharth Rout, Jan 31 '14 at 21:23

Alex D · Accepted Answer · 2014-01-31T21:44:47.320

3

In your if statements you're just referencing i, not the value contained in Column K and Row i which is what you seem to want.

So change the if statements from:

If i > 5 Then
'and
ElseIf i = 4 Then

to:

If Range("K" & i).Value > 5 Then
'and
ElseIf Range("K" & i).Value = 4 Then

For all your if statements. Also change your first and final cell statements. They might, work, but I know these will:

finalCell = ActiveSheet.Range("K" & ActiveSheet.Rows.Count).End(xlUp).Row
'and 
firstCell = 2

edited Jan 31 '14 at 21:44

answered Jan 31 '14 at 21:39

Alex D

696
6
13

2

+ 1 For clarifying what is the error in the code :) However... `finalCell = ActiveSheet.UsedRange.Rows.Count` Nooooooo :) Please never do that :) See the first link I gave below the question. – Siddharth Rout Jan 31 '14 at 21:42
Thank you, I'll update my code. That statement always worked well for me, I'll definitely use that for the rest of my time! – Alex D Jan 31 '14 at 21:43
2

Wow, thanks a ton. I switched this info in and works perfectly. Thanks to both of you for the links, code, and time. I already learned a lot. – ZubaZ Jan 31 '14 at 21:55

Siddharth Rout · Answer 2 · 2014-01-31T21:54:05.323

Since the two links which I mentioned in the comment do not cover about auto filter, see this example. Also What if the number is 5? Your code doesn't handle that. Did you by any chance mean ">4"? If yes, then change ">5" to ">4" in the code below.

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long, i As Long
    Dim rng As Range

    '~~> Change this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Remove any filters
        .AutoFilterMode = False

        lRow = .Range("K" & .Rows.Count).End(xlUp).Row

        With .Range("K1:K" & lRow)
            For i = 1 To 4
                .AutoFilter Field:=1, Criteria1:="=" & i

                Set rng = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow

                If Not rng Is Nothing Then
                    Select Case i
                        Case 1: rng.Font.Color = RGB(112, 48, 160)
                        Case 2: rng.Font.Color = RGB(0, 112, 192)
                        Case 3: rng.Font.Color = RGB(0, 176, 80)
                        Case 4: rng.Font.Color = RGB(226, 107, 10)
                    End Select

                    Set rng = Nothing
                End If

                ws.AutoFilter.ShowAllData
            Next i

            .AutoFilter Field:=1, Criteria1:=">5" '<~~ OR "<4" ???
            Set rng = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
            If Not rng Is Nothing Then rng.Font.Color = RGB(255, 0, 0)
        End With

        '~~> Remove any filters
        .AutoFilterMode = False
    End With
End Sub

After I ran the working macro the first time I realized I forgot about if it equals 5. Thanks! I have updated this as well and all lines formatted exactly as I had hoped. Thanks for help!! — ZubaZ, Jan 31 '14 at 21:56
You are welcome. One more tip before you go :) Whenever you are declaring variables which will hold the rows, never declare it as `Integer`. Declare it as a `Long` else you may get `Overflow` error in xl2007+ if you are working with bigger number of rows... — Siddharth Rout, Jan 31 '14 at 21:58

Excel VBA - Loop needed for a column

2 Answers2

Linked