Warehouse map in Excel Query

[Harperspace]

Dear All

1) I've used an excel spreadsheet to map out one of our warehouses.
If I say that my example warehouse has 3851 shelves (555 are empty)
one cell per client/shelf and I have 15 clients in total - I'd like t
be able to (when data is delivered) add the name of a client or indee
new clients name and the background color of a cell automaticall
colours itself with its respective colour. Also when data is remove
from a shelf and is now available to be filled by a new or existin
client, when I remove the text the cell resorts back to having n
background colour.

2) Ideally I'd love to attach the example spreadsheet - any way o
doing this or would that be outside the forum?
If this procedure is complicated I'll drop it & just fill in manuall
as I'm doing to date. I think I've searched through all your forums!

Many thanks

harperspac

 

[Glen Mettler]

If you need 15 different colors (one for each client), you will need vba to
do it. If you only need 2 colors (empty, not-empty), then use conditional
formatting - it requires no vba)

If you need many colors you could do something like this:
'assumes data rows start at 3 and client column is A

LastRow = Cells(Rows.Count, 1).End(xlUp).Row
for i = 3 to LastRow
DO CASE
CASE Cells(i,1).value = "Client 1"
cells(i,1).select
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 5
End With
CASE Cells(i,1).value = "Client 2"
Same code, different values
CASE Cells(i,1).value = "Client 3"
etc

End Case
next i

Hope this helps

Glen

"Harperspace" <[email protected]>
wrote in message
news:[email protected]...

 

[K Dales]

Hard to give any detailed answer without knowing more. The main question is
how you decide which shelf(cell) to use when you have a new delivery. Also,
how do you plan on assigning colors for new clients - would this be a random
choice?

My approach would be:
1) Have a list of your clients on a separate worksheet tab along with any
other info needed, and also use a cell in this list set to the proper color
that you can use both as a color key to your warehouse map and as a cell that
can be copied/paste format in order to apply that client's color to the cells
in the map.
2) Build a userform for entering deliveries/pick ups. You could use a
combobox linked to your client list for selection of the client (along with
an "add new" option).
3) Once the userform is filled in, use a command button to run code that
applies whatever logic you use to decide which shelf/cell gets filled (or
emptied). If filled, copy the color-formatted cell from your client list and
paste it in the map to create the proper color. If removed, reset the cell
to the default format, e.g. MapCell.Interior.ColorIndex = xlColorIndexNone.

This just sketches out an approach to doing what you describe; the
individual elements (list format, userform, combobox, etc) you can find other
examples in this newsgroup that would help you put them together. The only
part that would be particular to your need is the logic behind the code that
determines how to find the proper cell in your map; only you know how you
are doing this and without understanding your procedure I can't tell you how
to write code that would do it for you.