Change events puzzle me...
Posted on 2014-01-22
I have been learning vba now for about 1 year and the light is slowly going on but not burning bright at all...so I am still very "beginner-ish" but am getting better.
In that time I have gotten to know how to handle ranges and rows quite well per my previous lot of questions.
Having said that I have come to somewhat of a cross roads with my project and I have to wonder is there a better way.
Having said that I am going to describe in detail what I am trying to do and see if there is indeed a better way.
So without further adieu here is what I have.
I have a data set that dynamically changing all of the time.
Let's say that there are 1000 rows.
What I want to know is:
Let's say that there is a change in the row 24, column G. Let's say that in Column G there is a simple formula that says if e24>f24 then 1, otherwise "". Well, e24 suddenly = 9 and there is nothing in f24 so G24 = 1. This is the kick off to the change event. The rest of the code after this I am handle. I even have the code needed to "reset" G24 for the next time.
What I do not need is for any other row to be affected whatsoever. I am only interested in row 24 because G24 fired.
Now if G56, which also has a similar formula to G24 (all cells in column G do between G10:G1010) then I would like to see a change for only things in row 56.
...and so on down the whole range.
What I DO NOT WANT IS A LOOP. THEY TAKE TOO LONG PLEASE AND THANK YOU.
I was playing around with:
Range("G10:G1010").specialcells(xlCellsTypeFormula, 2).Calculate = "1"
...but I am not sure how to manipulate it further than that.
So in short, I need a "change event" or "calculate event" or something else that is lightning fast for these ranges. What ever happen in that particular row on affects that particular row and the rest of the range is unaffected.
Any help or illumination on this problem would be much appreciated.