excel 2010 need help with figetr for blanks and set backgroud color 1

[leo57]

the column I am looking in is M
I want to find both the letter"N" and blankc
this code works to filter the items bbut changes the backgroud color of all the cells to yellow
I tried to use this but it give object rrquired error
ActiveSheet.Range("M1:M500").AutoFilter Field:=ColumnAwarded, Criteria1:="=N", _
Operator:=xlOr, Criteria2:="="

ActiveSheet.Range("M1:M500").AutoFilter Field:=ColumnAwarded, Criteria1:="=N", _
        Operator:=xlOr, Criteria2:="="
    
    'Range("M6").Select
    ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        '.TintAndShade = 0
        '.PatternTintAndShade = 0
    End With

 

[SkipVought]

hi,

what is the value of ColumnAwarded?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

You may try something simple like this:

Dim i As Integer

For i = 1 To 500
    If Range("M" & i).Value = "N" Or Trim(Range("M" & i).Value) = "" Then
        Range("M" & i).Interior.Color = vbYellow
    End If
Next i

End Sub

Have fun.

---- Andy

 

[leo57]

sweet Andrzejek, I like a pure and simple answer and code
have a star

 

[Andrzejek]

I am sure there is a better, a ‘non-VBA’ way to do this, like Conditional Formatting.

Have fun.

---- Andy