I posted this question in the "Excel Programming" thread, but this seems
to be too simple for that thread.

I have a list that looks like this:

A...........B...........C...........D
Item......Size1......Size2.....Size3 (Header)
Item1.......$2........$3.........$4
Item2.......$1........$2.........$3
Item3.......$4........$5.........$6

"Item2" only comes in the first two sizes (Size1, and Size2), so I want
to add a column where I would specify the max size for "Item2", and a
macro would delete the Size3 price for Item2. (I would do this to all
items in my list)

Something like this:

A...........B...........C...........D...........E
Item......Size1......Size2.....Size3....MaxSize (Header)
Item1.......$2........$3.........$4...........3
Item2.......$1........$2.......................2
Item3.......$4........$5.........$6...........3

Thank You,
Leibtek


--
Leibtek
------------------------------------------------------------------------
Leibtek's Profile: http://www.excelforum.com/member.php...o&userid=32287
View this thread: http://www.excelforum.com/showthread...hreadid=571193

Try the following user defined function which you need to place on the
tab corresponding to your sheet of data (e.g. sheet1) in the VBA
editor.

Private Sub Worksheet_Change(ByVal Target As Range)
MaxColumn = 5
If Target.Column = MaxColumn And Target.Columns.Count = 1 And
Target.Rows.Count = 1 And IsNumeric(Target) Then
For N = MaxColumn - 1 To Int(Target.Value) + 2 Step -1
Cells(Target.Row, N).ClearContents
Next N
End If
End Sub


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=571193

Thanks, - Worked Great.


--
Leibtek
------------------------------------------------------------------------
Leibtek's Profile: http://www.excelforum.com/member.php...o&userid=32287
View this thread: http://www.excelforum.com/showthread...hreadid=571193