Hi Tom (Bob)

thanks for your responses - the bit i didn't do when i tried this was use
the "target" ... all makes sense now.

thanks for your time & asssistance.
Regards

"Tom Ogilvy" wrote in message
...
Some corrections (inattention to detail).

ActiveCell should be Target and since we already had assumed B18 for
category and C18 for Month

Private Sub Worksheet_Change(ByVal Target As Range)

Dim res as Variant, res1 as Variant
Dim rng as Range
if Target.count 1 then exit sub
if Target.Address = "$C$18" and not isempty(Range("B18")) then
res = Application.Match(Range("B18"),Range("Categories") ,0)
res1 = Application.Match(Range("C18"),Range("Months"),0)
if not iserror(res) and not iserror(res1) then
set rng =Range("Product!A7").offset(res,res1)
if rng.Interior.ColorIndex = 6 then
msgbox "not available"
else
Application.EnableEvents = False
Target.Value = rng.Value
Application.EnableEvents = True
rng.Interior.ColorIndex = 6
end if
Else
Msgbox "location not found"

End if
End if

End Sub

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
Category Month Num Items
d/d list d/d list offset & match

If using Excel 2000 or later, you would use the Change Event


assume the month dropdown is in cell f5 and the category dropdown in E5
Private Sub Worksheet_Change(ByVal Target As Range)

Dim res as Variant, res1 as Variant
Dim rng as Range
if Target.count 1 then exit sub
if Target.Address = "$F$5" and not isempty(Range("E5")) then
res = Application.Match(Range("B18"),Range("Categories") ,0)
res1 = Application.Match(Range("C18"),Range("Months"),0)
if not iserror(res) and not iserror(res1) then
set rng =Range("Product!A7").offset(res,res1)
if rng.Interior.ColorIndex = 6 then
msgbox "not available"
else
Application.EnableEvents = False
activecell.Value = rng.Value
Application.EnableEvents = True
rng.Interior.ColorIndex = 6
end if
Else
Msgbox "location not found"

End if
End if

End Sub

--
Regards,
Tom Oiglvy

"JulieD" wrote in message
...
Hi Tom

this is my problem, i would like it to execute when the the user has
selected the month (after selecting the category) so i'm guessing i
need
to
use the worksheet_selectionchange event but i don't know how to get it
to
work.

Cheers
JulieD

--snip--