type mismatch error

[MuskyMan]

Hi. Running into a Type Mismatch error when running the loop below.

I receive a rather large spreadsheet (up 9000 rows on certain months) monthly and I need to highlight certain amounts. Problem lies in that some of the cells have text in lieu of numbers. I just want the Sub to ignore the cells with text.

Thanks in advance for any input that you may have.


Sub Highlite()
Dim i As Long

For i = Cells(Rows.Count, "B&quot.End(xlUp).Row To 1 Step -1
If Cells(i, "C&quot.Value > 1000 Then Cells(i, "C&quot.Interior.ColorIndex = 6

End Sub

Next i

 

[robcarr]

in your post next I is outside the sub. I am sure htis is a typo

Thanks Rob.

 

[robcarr]

have checked the coding and it highlights any value over 1000 for me if column b has data

Sub Highlite()
Dim i As Long

For i = Cells(Rows.Count, "B&quot.End(xlUp).Row To 1 Step -1
If Cells(i, "C&quot.Value > 1000 Then Cells(i, "C&quot.Interior.ColorIndex = 6
Next i
End Sub



Thanks Rob.

 

[MuskyMan]

Rob, the loop works great until it reaches a cell where my salesperson types in "Nothing this month". This were I get the error. I can always use On Err Resume Next, however, I would like to eventually delete the text in the cell.. I don't know how to deal with both Text and Numbers in the same loop..

Thanks

 

[PHV]

You can play with IsNumeric

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[robcarr]

i had "Nothing in this month" in the column and it didnt error out, but PHV has suggested to do a check on isnumeric and then if not skip cell.

rob.

Thanks Rob.

 

[MuskyMan]

Thanks Rob and PHV, IsNumeric was the last step that I was looking for...