Obtaining the cell value when the numbers in a particular column switches from positive to negative and vice versa (Excel)

[Rickz]

Hello,
I have a column in Excel with both positive and negative numbers. I would like to receive through VBA a notification on screen every time in the list a number switches sign from one row to the other. The notification is different with a change from positive to negative than from negative to positive. It would also have to indicate at what row that change happens. Also the corresponding values from the cells (of a column K) corresponding to that rows (in which the sign change occurs).

Thank you for your help.

 

[Rickz]

Hello,

I have attached a screenshot of the concept which I have mentioned above. In the picture, the value in Column X is changing from positive to negative at one point and at that time I would like to get the value in the Column K (1034.91) on the same row where this sign change occcurs. Unfortunately, I have to deal with around thousand values in Column X and this sign change occurs for a lot of times from positive to negative and vice-versa. So I would like to know a VBA code to get the cell values in Column K whenever there is a sign change occurs in column X (at the same row) as in the picture.

I hope someone can help me with this issue.

Thanks

 

[mumps]

Is the change from positive to negative or negative to positive the result of a formula in column X? If not, what causes the change?

The notification is different with a change from positive to negative than from negative to positive

How is the notification different with each change?

It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Rickz]

Yes the sign change happens as a result of the formula in column X. The values in column X are calculated with a formula that contains all the preceding columns values.

 

[bebo021999]

I try to color the cells with purple (positive to negative) and green (neg to pos)

Option Explicit
Sub test()
Dim lr&, i&, j&
lr = Cells(Rows.Count, "X").End(xlUp).Row
    For i = 2 To lr - 1
        If Range("X" & i).Value > 0 And Range("X" & i + 1).Value < 0 Then
            Range("X" & i).Resize(2, 1).Interior.ColorIndex = 7
            Range("K" & i).Resize(2, 1).Interior.ColorIndex = 7
        ElseIf Range("X" & i).Value < 0 And Range("X" & i + 1).Value > 0 Then
            Range("X" & i).Resize(2, 1).Interior.ColorIndex = 4
            Range("K" & i).Resize(2, 1).Interior.ColorIndex = 4
        End If
   Next
End Sub

 

[Peter_SSs]

The values in column X are calculated with a formula that contains all the preceding columns values.

All the preceding columns values (from column A to column W) or perhaps just M:W?

 

[Rickz]

All the preceding columns values (from column A to column W) or perhaps just M:W?

It is only from columns L to W

 

[Rickz]

I try to color the cells with purple (positive to negative) and green (neg to pos)

Option Explicit
Sub test()
Dim lr&, i&, j&
lr = Cells(Rows.Count, "X").End(xlUp).Row
    For i = 2 To lr - 1
        If Range("X" & i).Value > 0 And Range("X" & i + 1).Value < 0 Then
            Range("X" & i).Resize(2, 1).Interior.ColorIndex = 7
            Range("K" & i).Resize(2, 1).Interior.ColorIndex = 7
        ElseIf Range("X" & i).Value < 0 And Range("X" & i + 1).Value > 0 Then
            Range("X" & i).Resize(2, 1).Interior.ColorIndex = 4
            Range("K" & i).Resize(2, 1).Interior.ColorIndex = 4
        End If
   Next
End Sub

View attachment 66775

Hello @bebo021999 ,

I am also getting the cells in different colours by using the code, but what I actually required is to simply get the corresponding values in column K either in a notification box or if possible in a separate sheet. (rather than fromatting with a colour)

 

[Peter_SSs]

It is only from columns L to W

Thanks for the clarification.

Any chance we could get some small sample data and expected results with XL2BB so that we can copy for testing possible solutions?