need help creating highlight macro based on range of values

[sinamon]

Need help creating highlight macro, I looked over some of the examples here for highlighting cells but cant find and have not been able to adjust one to fits my needs.
I want a macro triggered by a command button to search the range of cells from
N:5-N:200 and find values 1000-5000 and highlight the cell next to it in column O in yellow and Highlight values 5000 or greater in Green. Column O contains text.

Thanks if some one can help

[Bob Phillips]

[vba]

For Each cell In Range("N5:N200")

If cell.Value >= 1000 And cell.value <=5000 Then

cell.Offset(0, 1).Interior.Colorindex = 37
End If
Next cell
[/vba]

[sinamon]

thanks for the quick response, im a total vba noob so im not even sure what to put before this code, i tried with just your code an got error and then I added code In the beginning from a working marco I have and still get error

here what I tried
[VBA]Sub Highlight()
Dim rValues As Range
Set rValues = ThisWorkbook.Sheets(1).Range("N5:N200")
For Each cell In Range("N5:N200")

If cell.Value >= 1000 And cell.Value <= 5000 Then

cell.Offset(0, 1).Interior.ColorIndex = 37
End If
Next cell
End Sub[/VBA]

be great help if you can give me the way it should look exactly in vba. Thanks for your help

[sinamon]

Ok it seems that it works now with the code above. but its not highlighting all the specified rows in column O its seems to stop highlighting at row 37 even though after this row there are values between 1000-5000.

[Bob Phillips]

You never mentioned column 0, just c & D.

Can you post a workbook?

[sinamon]

Ok I solved the first problem, turns out it stopped highlighting because
one of the cells value was text and not numeric. but how do I add another
one. I try but keep getting the next without for
heres what I have.
[VBA]Sub Highlight()

For Each cell In Range("N5:N200")

If cell.Value >= 1000 And cell.Value <= 50000 Then

cell.Offset(0, 1).Interior.ColorIndex = 37

If cell.Value >= 10000 And cell.Value <= 50000 Then

cell.Offset(0, 1).Interior.ColorIndex = 22
End If
Next cell
End Sub[/VBA]

Thanks

[Bob Phillips]

What exactly are you trying to do, you have jsut added exactly the same condition?

[sinamon]

my mistake im trying to add another condition but i pasted the same thing, my bad but
im still getting the error when i write it like this
[VBA]Sub Highlight()

For Each cell In Range("N5:N200")

If cell.Value >= 1000 And cell.Value <= 5000 Then

cell.Offset(0, 1).Interior.ColorIndex = 6

If cell.Value >= 10000 And cell.Value <= 100000 Then

cell.Offset(0, 1).Interior.ColorIndex = 50
End If
Next cell
End Sub [/VBA]
but i get the next without for error

[Bob Phillips]

[vba]

Sub Highlight()

For Each cell In Range("N5:N200")

If cell.Value >= 1000 And cell.Value <= 5000 Then

cell.Offset(0, 1).Interior.ColorIndex = 6

ElseIf cell.Value >= 10000 And cell.Value <= 100000 Then

cell.Offset(0, 1).Interior.ColorIndex = 50
End If
Next cell
End Sub[/vba]