Macro for finding the first negative number

[MK3]

So I am trying to write a macro that will search the entire sheet and find the first negative number in a row, then move ten cells to the left and highlight that cell green. I have a full sheet projecting orders and this will help out a ton I am just copletely lost and I really need some help!

Any help is welcome!

 

[Trevor G]

welcome to the forum,

This would cause an issue if the first column has negative numbers as it wont beable to move to the left?

 

[MK3]

Well the projectios first negaiteve numbers are almost 30 cells out form column A so there is no issue with having the 10 cells to move left. If that makes sense...

and thanks for welcoming me!

 

[Trevor G]

Look at this snipped of code then

Sub Macro1()
Dim ran As Range
For Each ran In Range("K:K")
If ran.Value < 0 Then
ran.Offset(0, -10).Interior.Color = vbRed
End If
Next
End Sub

 

[MK3]

So I tried entering that and it keeps telling me I have a Run time error "13"

Debug takes me here... If ran.Value < 0 Then

 

[MK3]

Nevermind got it working!!!! Thank you sooooo much! your a life saver!

 

[Greg Truby]

Hi MK3 -

The macro provided will do the trick if your values are static. Do note that should the values change you would need to run the macro again (also, the macro does not reset the red cells to clear, which you would need to do should a subsequent run be needed).

If you need a solution that is dynamic, then I would explore conditional formatting.

Assuming you wish to start applying this based on values from K:K rightwards, then select Cells A1:____ and in the conditional formatting dialog set it to use this formula:

=(COUNTIF($K1:K1,"<0")-COUNTIF($J1:J1,"<0"))*COUNTIF($K1:K1,"<0")=1

And pick the color you want and add salt and pepper to taste.

 

[Greg Truby]

Sorry, I should also have noted - the macro does not do what you asked - it finds the first negative number in a column and then colors 10 to the left. You asked to find the first negative number in a row. Furthermore it does not stop if more than one negative number is found.

 

[MK3]

Yeah good looking out Greg!

And I found that out once I started running it that it highlights all 10 cells so now I am trying to tweek it to make it do exactly what I need, and for some reason it stops half way through the page sayng there is a type mismatch error and it wont finish running the second half of the page...any suggestions on that one?

 

[Greg Truby]

Regarding the type mismatch - my guess would be that the cell being examined in the "< 0" comparison contains a non-numeric that Excel cannot coerce into a numeric for purposes of the comparison test.

The way to find out is to set watches on

• ran.value
• ran.address(0,0)

and then inspect the error-causing cell when the code bombs.