PDA

View Full Version : [SOLVED] Using Items sequence numbers in order



oam
06-11-2014, 07:32 PM
I work in a facility where items we process must be used in order by sequence number. The items are issued out of the storage room by scanning the sequence number(s) and the employee signing the issue log (see below for example log), there by taking possession of the item(s).
I would like to know, is it possible to have a formula or macro to look at the sequence number column to identify entries where items are issued out of sequence and have the cell in question change color there by alerting the manager to a problem? The column (E column) is quite long, as much as 4000 rows in the sequence number column. In addition to the Issue (out to employees), sometimes the employee(s) are unable to complete all the items issued to them and the items are returned to the storage room shown in the log as Rcv. (back to storage room) to be issued to the next shift.

Thank you for any assistance you can provide.

11806

ranman256
06-12-2014, 05:23 AM
Send the workbook and I'll take a crack at it.

Bob Phillips
06-12-2014, 08:47 AM
Use conditional formatting with a formula of

=E5<E4

and format as you desire

oam
06-12-2014, 09:57 PM
ranman256 (http://www.vbaexpress.com/forum/member.php?53149-ranman256),

Attached is a copy of the balance sheet

Thanks for any and all help

snb
06-13-2014, 01:51 AM
Conditional formatting for E5:E6000:

=MOD(ROW(E5);2)*(E5-E4<>1)=1

ranman256
06-13-2014, 08:03 AM
oam, I had a full working macro, but I was still in .xlsx and saved it and excel erased it because it wasnt a .xlsm! #$@!
I'll try to recreate.

ranman256
06-13-2014, 08:58 AM
11816

Here is the basic macro.
I did not recreate the logic for errors (see the select statement) but you can do that.

oam
06-16-2014, 07:48 PM
I want to thank all of you for helping me solve my problem, this is the best Excel forum!