Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup value and change background colour
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup value and change background colour
=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.Colo rIndex = 6 End if -- Regards, Tom Ogilvy "JulieD" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup value and change background colour
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup value and change background colour
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 "JulieD" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup value and change background colour
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. -- Regards, Tom Ogilvy "JulieD" wrote in message ... 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 "JulieD" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup value and change background colour
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" wrote in message ... 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. -- Regards, Tom Ogilvy "JulieD" wrote in message ... 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 "JulieD" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup value and change background colour
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 -- Regards, Tom Ogilvy "JulieD" wrote in message ... 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" wrote in message ... 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. -- Regards, Tom Ogilvy "JulieD" wrote in message ... 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 "JulieD" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup value and change background colour
Hi Tom
sorry to be dense but WHERE do i put this code? Cheers JulieD "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "JulieD" wrote in message ... 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" wrote in message ... 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. -- Regards, Tom Ogilvy "JulieD" wrote in message ... 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 "JulieD" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup value and change background colour
Only you can answer that. When do you want it to execute? What will make
the code take the programmed action? -- Regards, Tom Ogilvy "JulieD" wrote in message ... Hi Tom sorry to be dense but WHERE do i put this code? Cheers JulieD "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "JulieD" wrote in message ... 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" wrote in message ... 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. -- Regards, Tom Ogilvy "JulieD" wrote in message ... 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 "JulieD" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup value and change background colour
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-- |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup value and change background colour
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) "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-- |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup value and change background colour
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) "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-- |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup value and change background colour
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-- |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup value and change background colour
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-- |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup value and change background colour
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-- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I change worksheet background colour | Setting up and Configuration of Excel | |||
Change background colour | Excel Worksheet Functions | |||
Change Font Colour and Background If Q | Excel Worksheet Functions | |||
change a cell background colour to my own RGB colour requirements | Excel Discussion (Misc queries) | |||
change background colour | Excel Discussion (Misc queries) |