lookup value and change background colour

[JulieD]

Hi All

How do i (with code), lookup a cell - using the offset & match function -
and set the background colour to yellow..

the function i'm wanting to use is
=Offset(Product!$A$7,Match(B18,Categories,0),Match(C18,Months,0))

thanks
JulieD

 

[Tom Ogilvy]

=Offset(Product!$A$7,Match(B18,Categories,0),Match(C18,Months,0))

Dim res as Variant, res1 as Variant
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
Range("Product!A7").offset(res,res1).Interior.ColorIndex = 6
End if

 

[Bob Phillips]

icol = WorksheetFunction.Match(Range("C18"), Range("Months"), 0)
irow = WorksheetFunction.Match(Range("B18"), Range("Categories"), 0)
Worksheets("Product").Range("A7").Offset(irow, icol).Interior.ColorIndex
= 6


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[JulieD]

Hi guys

thanks it works great ..

now what i would like do is check to see if the background of a cell is
yellow as part of a validation process later in the workbook.

basically
i'm looking up two items - category & month and if the background IS yellow
i would like a warning message to come up and say "unavailable".
i think i might need to do it as a worksheet_selectionchange on cells D18 to
D25 but i've not (successfully) done something like this and would
appreciate any pointers.

Regards
JulieD

 

[Tom Ogilvy]

If activeCell.Interior.ColorIndex = 6 Then
msgbox ActiveCell.Address & " is yellow"
End If

If you are identifying the cell with a category and a month, then you could
use the same approach as posted, using match.

If you are looping through the categories and months, then you should have
their location already.

If you want to use an event, then it sounds like you are selecting a cell,
but then wouldn't it be obvious that the cell is yellow?

In any event, the event has an argument Target indicating the cell that
triggered the macro

If Target.Interior.ColorIndex = 6 Then
msgbox Target.Address & " is yellow"
End If

Perhaps a clearer question on what you need assistance on would provide a
more focused response.

 

[JulieD]

Hi Tom

sorry for not stating the situation more clearer
basically i have two sheets
the first sheet is where i look up the category and the month (from data
validation drop down lists) and fill in the number of items available (found
on the second sheet)

e.g.
Category Month Num Items
d/d list d/d list offset & match
formula

then i want the number of item in the previous step to go yellow on the
second sheet

month1 month2 month3
category b/g yellow (previously
ordered)

but then i want to stop these being re-ordered on the first sheet later on
in another order by checking (after the category & month is chosen) if the
number of items has a yellow background

Category Month Num Items
d/d list d/d list msgbox
"Unavailable"

does this make any more sense
what i'm trying to do is after the month is chosen check to see if the
background of the thing to be returned to the num items column is yellow -
if it is bring up msgbox if not fill in num items

Cheers
julieD

 

[Tom Ogilvy]

Dim res as Variant, res1 as Variant
Dim rng as Range
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
activecell.Value = rng.Value
rng.Interior.ColorIndex = 6
end if
Else
Msgbox "location not found"

End if

 

[JulieD]

Hi Tom

sorry to be dense but WHERE do i put this code?

Cheers
JulieD

 

[Tom Ogilvy]

Only you can answer that. When do you want it to execute? What will make
the code take the programmed action?

 

[JulieD]

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--

 

[Bob Phillips]

Julie that sounds as if it should go in the

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Try again.

That sounds as if it should go in the sheet module of the sheet that
contains the dropdowns. So on Sheet1, add this perhaps

Private Sub Worksheet_Change(ByVal Target As Range)
Dim res As Variant, res1 As Variant
Dim rng As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address = "$C$18" 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 = Worksheets("Product").Range("A7").Offset(res, res1)
If rng.Interior.ColorIndex = 6 Then
MsgBox "not available"
Else
ActiveCell.Value = rng.Value
rng.Interior.ColorIndex = 6
End If
Else
MsgBox "location not found"
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

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

 

[Tom Ogilvy]

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

 

[JulieD]

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