Usedrange except a few cells

Question

I'm trying to colour-code the cells in Column C, one for when it says "Yes" and one for "No". The below code works. But I want it to colour the cells C4 and down, not C1,C2 and C3. Any Tips? Also any tips how I can Colour-code cells based on a result from another worksheet?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For Each cell In ActiveSheet.UsedRange.Columns("C").Cells
    If Len(cell.Value) > 2 Then
       cell.Interior.ColorIndex = 3
    ElseIf Len(cell.Value) < 3 Then
    cell.Interior.ColorIndex = 4
        End If
    Next
End Sub

never use USEDRANGE. you should look at this http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba — Goos van den Bekerom, Sep 24 '14 at 13:44

score 1 · Answer 1 · answered Sep 24 '14 at 13:50

1

Use:

N = Cells(Rows.Count, "C").End(xlUp).Row
For Each cell In Range("C4:C" & N)

answered Sep 24 '14 at 13:50

Gary's Student

95,722
10
59
99

score 0 · Answer 2 · answered Sep 24 '14 at 14:09

Why not just add the extra if clause?

IE:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For Each cell In ActiveSheet.UsedRange.Columns("C").Cells
    If cell.Row <> 1 And cell.Row <> 2 And cell.Row <> 3 Then
        If Len(cell.Value) > 2 Then
           cell.Interior.ColorIndex = 3
        ElseIf Len(cell.Value) < 3 Then
            cell.Interior.ColorIndex = 4
        End If
    End If
Next
End Sub

Usedrange except a few cells

2 Answers2