Changing cells in previous rows based on new data

[sekrah]

Kinda difficult to explain, but I was wondering if someone could help me out here.

I need either a floating toolbar/button (or frozen button at the top), that when clicked, will read the last row of data and compare it to previous data in the sheet. If certain cells in this last line of data match certain cells in previous rows, I want the contents of another cell in the previous row(s) changed.

For example with my attachment below, if I'm currently entering data on row 38, and I enter DEF in column E, 1987 in column F, and I am not putting this row "In Zone" (which would be a Y in column M. Column M will be blank on this row), I want to be able to hit a button that reads this bottom entry and compares it to the rest of the sheet (i.e, it would see that these two cells match row 27 and row 36). I then want the Y automatically removed from the column M from rows 27 and 36.

Note: I have the date running past line 38 intentionally. That column and other columns are irrelevant and shouldn't trigger the "search and destroy". I just need the very last row of column E and F that has content in it, to be looked at and compared to the rest of the sheet when the button is clicked.

I'm not even sure if this is possible or how I would even begin to get this right. Any help would be greatly appreciated.

[JBeaucaire]

By freezing the panes at row 2 so the "title row1" is always visible, you can drop a macro button on that row, too, and it will always be visible.

Then this macro should do what you want:

Please Login or Register  to view this content.

[sekrah]

Okay, thanks for the help Jerry. For the most part that definently does what I want it to do . The only hiccup is that if it doesn't find a matching cell, I'm getting an error box popup that says:

`Run-time error 1004'
No cells were found

and my sheet is kinda messing up after that error. How do I remove this error box. I'd like it to just do nothing if it doesn't find a match in the sheet.

[JBeaucaire]

Try this;

Please Login or Register  to view this content.

[sekrah]

Jerry, that 2nd code solved the problem with the "Y" not being in the M column. I'm no longer getting that error, but I'm still getting the error if the button is clicked and the new entry on the bottom row of E and F is the first entry for that Product and Code. If there are no matches with the rest of the sheet, the error box is coming up.

Thank you for all your help with this. It's unbelievably appreciated.

[sekrah]

I think I got it by taping into the Subtotal function and counting the visible rows left and skipping over the cell change

Please Login or Register  to view this content.

Is this decent coding? or is there a better way to do this. Thanks for all your help.

[JBeaucaire]

Looks nice. Well done.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.