Warehouse map in Excel Query

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 to be able to (when data is delivered) add the name of a client or indeed new clients name and the background color of a cell automatically colours itself with its respective colour. Also when data is removed from a shelf and is now available to be filled by a new or existing client, when I remove the text the cell resorts back to having no background colour.

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

Many thanks

harperspace

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" <Harperspace.24xv5b_1142808603.7212@excelforum-nospam.com>
wrote in message
news:Harperspace.24xv5b_1142808603.7212@excelforum-nospam.com...
>
> 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 to
> be able to (when data is delivered) add the name of a client or indeed
> new clients name and the background color of a cell automatically
> colours itself with its respective colour. Also when data is removed
> from a shelf and is now available to be filled by a new or existing
> client, when I remove the text the cell resorts back to having no
> background colour.
>
> 2) Ideally I'd love to attach the example spreadsheet - any way of
> doing this or would that be outside the forum?
> If this procedure is complicated I'll drop it & just fill in manually
> as I'm doing to date. I think I've searched through all your forums!
>
> Many thanks
>
> harperspace
>
>
> --
> Harperspace
> ------------------------------------------------------------------------
> Harperspace's Profile:
> http://www.excelforum.com/member.php...o&userid=32608
> View this thread: http://www.excelforum.com/showthread...hreadid=524101
>
>

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.
--
- K Dales


"Harperspace" wrote:

>
> 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 to
> be able to (when data is delivered) add the name of a client or indeed
> new clients name and the background color of a cell automatically
> colours itself with its respective colour. Also when data is removed
> from a shelf and is now available to be filled by a new or existing
> client, when I remove the text the cell resorts back to having no
> background colour.
>
> 2) Ideally I'd love to attach the example spreadsheet - any way of
> doing this or would that be outside the forum?
> If this procedure is complicated I'll drop it & just fill in manually
> as I'm doing to date. I think I've searched through all your forums!
>
> Many thanks
>
> harperspace
>
>
> --
> Harperspace
> ------------------------------------------------------------------------
> Harperspace's Profile: http://www.excelforum.com/member.php...o&userid=32608
> View this thread: http://www.excelforum.com/showthread...hreadid=524101
>
>