PDA

View Full Version : need help creating highlight macro based on range of values



sinamon
05-30-2011, 07:24 AM
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
05-30-2011, 08:06 AM
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

sinamon
05-30-2011, 08:54 AM
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
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

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

sinamon
05-30-2011, 10:04 AM
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
05-30-2011, 11:00 AM
You never mentioned column 0, just c & D.

Can you post a workbook?

sinamon
05-30-2011, 11:01 AM
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.
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

Thanks

Bob Phillips
05-30-2011, 12:40 PM
What exactly are you trying to do, you have jsut added exactly the same condition?

sinamon
05-30-2011, 01:33 PM
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
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
but i get the next without for error

Bob Phillips
05-30-2011, 10:26 PM
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