VBA: notify when number switches from positive to negative and vice versa (Excel)

[zeno]

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.
Thank you for your help.

 

[zeno]

This is working!
I may make a few smaller changes, but this is nice work!
Thanks again!

A small follow-up:
if I would like this message to be automatically triggered, how can that be done?

 

[Joe4]

if I would like this message to be automatically triggered, how can that be done?

Automatics triggers happen based on some "event" happening. This is what triggers the code to run. So what event do you want to trigger this code to run?

 

[zeno]

The event would be the change of sign. But maybe that would only happen if new data are coming in? For the moment I have an excel file with data, so the analysis can be done instantaneously with all data already available.
The trigger event would happen if new data inflow would generate new log lines. Maybe my follow-up question was meant for a later stage and is this too soon?

 

[Joe4]

It is possible that a Worksheet_Change event procedure will work for you. That is triggered anytime a value is manually changed on the spreadshseet.

Here is a write-up on all the event procedures: http://www.cpearson.com/excel/Events.aspx

 

[zeno]

Thanks for the Worksheet_Change event procedure. I'm reading it now.

Is it possible to make different log depending on the type of change of sign? So if the change is from - sign to + sign, the log could indicate that the number turned positive. And the other way around.

 

[Joe4]

Is it possible to make different log depending on the type of change of sign? So if the change is from - sign to + sign, the log could indicate that the number turned positive. And the other way around.

Of course. Just include the myCurrentSign and/or myLastSign variables in the statement where the log is written out. Give it a shot!

 

[zeno]

Yes indeed. That works

 

[zeno]

I have a question on the log:
When checking the log, I realize that it would identify as first switch of sign the first line with a number, i.e. from blank to a number (there are a few blank rows in the beginning). This is incorrect.
How can it identify only switches from numbers, not including blank cells?

 

[Joe4]

You can specify in the code where the data starts. Just update that line of code.

'   Designate which row to run on
    myStartRow = 2

 

[zeno]

I actually have a variable starting point. So in that case identification of non-blank cells, not including row 1 with a title, would be the most direct approach.
If such a non-blank cell identifier does not exist, I may have to find out a work around.