Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display background color of a cell in a Validated cell
Hi
I used validation function to create a drop down list. b3 to b42 uses the same formaula that shows a list from b47:b172. I added 7 or 8 background colors within the b47:b172 cells. is there anyway to get the background color to show when i select them in the b3 to b42 drop downs? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display background color of a cell in a Validated cell
no..
datavalidation just creates a small, basic listbox no customization possible 'in the box'. (unless you're a wizard in APIs and subclassing) -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Infoseeker wrote : Hi I used validation function to create a drop down list. b3 to b42 uses the same formaula that shows a list from b47:b172. I added 7 or 8 background colors within the b47:b172 cells. is there anyway to get the background color to show when i select them in the b3 to b42 drop downs? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display background color of a cell in a Validated cell
Then is there a way to create a conditional format for more then 3 colors
where a range b47:b58 is red, b58:b69 is blue etc? "keepITcool" wrote: no.. datavalidation just creates a small, basic listbox no customization possible 'in the box'. (unless you're a wizard in APIs and subclassing) -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Infoseeker wrote : Hi I used validation function to create a drop down list. b3 to b42 uses the same formaula that shows a list from b47:b172. I added 7 or 8 background colors within the b47:b172 cells. is there anyway to get the background color to show when i select them in the b3 to b42 drop downs? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display background color of a cell in a Validated cell
i think you know there isn't... BUT I think following will do what you want: Private Sub Worksheet_Change(ByVal Target As Range) 'if a cell has datavalidation with incell dropdown 'the cell will be colored the same as selected item 'in the list source range Dim ci As Long Dim vMatch As Variant With Target If .Count 1 Then Exit Sub On Error Resume Next If IsError(.Validation.Type) Then Exit Sub On Error GoTo 0 If .Validation.Type = 3 Then If Len(.Value) = 0 Then ci = xlNone Else vMatch = Application.Match(.Value, _ Range(.Validation.Formula1), 0) If IsError(vMatch) Then ci = xlNone Else ci = Range(.Validation.Formula1).Cells(vMatch, _ 1).Interior.ColorIndex End If End If .Interior.ColorIndex = ci End If End With End Sub -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Infoseeker wrote : Then is there a way to create a conditional format for more then 3 colors where a range b47:b58 is red, b58:b69 is blue etc? "keepITcool" wrote: no.. datavalidation just creates a small, basic listbox no customization possible 'in the box'. (unless you're a wizard in APIs and subclassing) -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam Infoseeker wrote : Hi I used validation function to create a drop down list. b3 to b42 uses the same formaula that shows a list from b47:b172. I added 7 or 8 background colors within the b47:b172 cells. is there anyway to get the background color to show when i select them in the b3 to b42 drop downs? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display background color of a cell in a Validated cell
I have given several example over the past two weks of how this might be
done. do a search on this. try a serach "enum eColor" in this ng "Infoseeker" wrote in message ... Then is there a way to create a conditional format for more then 3 colors where a range b47:b58 is red, b58:b69 is blue etc? "keepITcool" wrote: no.. datavalidation just creates a small, basic listbox no customization possible 'in the box'. (unless you're a wizard in APIs and subclassing) -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Infoseeker wrote : Hi I used validation function to create a drop down list. b3 to b42 uses the same formaula that shows a list from b47:b172. I added 7 or 8 background colors within the b47:b172 cells. is there anyway to get the background color to show when i select them in the b3 to b42 drop downs? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display background color of a cell in a Validated cell
here it is:
Using VBA. Add a standard code module and paste the following: Option Explicit Enum eColors Yellow = 10092543 Green = 13434828 Blue = 16777164 Grey = 12632256 Orange = 10079487 End Enum Sub MyConditionalFormatting(Target As Range) Dim cell As Range Dim clr As Long For Each cell In Target Select Case UCase(Trim(cell.Value)) Case "LUNCH": clr = eColors.Yellow Case "OFF": clr = eColors.Blue Case "HOLIDAY": clr = eColors.Grey Case "COURSE": clr = eColors.Orange Case Else: clr = eColors.Green End Select cell.Interior.Color = clr Next End Sub Sub test() MyConditionalFormatting Range("MyData") End Sub To test On a sheet, select a range, name it "MyData" type some values into some cells in th erange randomly, include: Holiday, Lunch, Off, Course run the Test procedure. This passes the range "MyData" to the procedure that then does the formatting. This example allows four colors, but you can see that its quite easy to adapt to use more colors. HTH Patrick Molloy Microaoft Excel MVP "Patrick Molloy" wrote in message ... I have given several example over the past two weks of how this might be done. do a search on this. try a serach "enum eColor" in this ng "Infoseeker" wrote in message ... Then is there a way to create a conditional format for more then 3 colors where a range b47:b58 is red, b58:b69 is blue etc? "keepITcool" wrote: no.. datavalidation just creates a small, basic listbox no customization possible 'in the box'. (unless you're a wizard in APIs and subclassing) -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Infoseeker wrote : Hi I used validation function to create a drop down list. b3 to b42 uses the same formaula that shows a list from b47:b172. I added 7 or 8 background colors within the b47:b172 cells. is there anyway to get the background color to show when i select them in the b3 to b42 drop downs? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display background color of a cell in a Validated cell
....actually, I just realised that it doean't answer the quaetion.
:( "Patrick Molloy" wrote in message ... I have given several example over the past two weks of how this might be done. do a search on this. try a serach "enum eColor" in this ng "Infoseeker" wrote in message ... Then is there a way to create a conditional format for more then 3 colors where a range b47:b58 is red, b58:b69 is blue etc? "keepITcool" wrote: no.. datavalidation just creates a small, basic listbox no customization possible 'in the box'. (unless you're a wizard in APIs and subclassing) -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Infoseeker wrote : Hi I used validation function to create a drop down list. b3 to b42 uses the same formaula that shows a list from b47:b172. I added 7 or 8 background colors within the b47:b172 cells. is there anyway to get the background color to show when i select them in the b3 to b42 drop downs? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display background color of a cell in a Validated cell
Hi I think i know what the program is suppose to do but when i pasted it in
it didn't work. My Values are in =B47:B212 and the data validation is a list in cells B3:b41 what am i doing wrong? "keepITcool" wrote: i think you know there isn't... BUT I think following will do what you want: Private Sub Worksheet_Change(ByVal Target As Range) 'if a cell has datavalidation with incell dropdown 'the cell will be colored the same as selected item 'in the list source range Dim ci As Long Dim vMatch As Variant With Target If .Count 1 Then Exit Sub On Error Resume Next If IsError(.Validation.Type) Then Exit Sub On Error GoTo 0 If .Validation.Type = 3 Then If Len(.Value) = 0 Then ci = xlNone Else vMatch = Application.Match(.Value, _ Range(.Validation.Formula1), 0) If IsError(vMatch) Then ci = xlNone Else ci = Range(.Validation.Formula1).Cells(vMatch, _ 1).Interior.ColorIndex End If End If .Interior.ColorIndex = ci End If End With End Sub -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Infoseeker wrote : Then is there a way to create a conditional format for more then 3 colors where a range b47:b58 is red, b58:b69 is blue etc? "keepITcool" wrote: no.. datavalidation just creates a small, basic listbox no customization possible 'in the box'. (unless you're a wizard in APIs and subclassing) -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam Infoseeker wrote : Hi I used validation function to create a drop down list. b3 to b42 uses the same formaula that shows a list from b47:b172. I added 7 or 8 background colors within the b47:b172 cells. is there anyway to get the background color to show when i select them in the b3 to b42 drop downs? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display background color of a cell in a Validated cell
Hi I think i know what it's trying to do however it didn't work. I have Data
Validations on B3:B42 and my range is from =B47:B212 are you trying to display the back ground colors of =B47:B212 when i select them in B3:B42? What am i doing wrong? "keepITcool" wrote: i think you know there isn't... BUT I think following will do what you want: Private Sub Worksheet_Change(ByVal Target As Range) 'if a cell has datavalidation with incell dropdown 'the cell will be colored the same as selected item 'in the list source range Dim ci As Long Dim vMatch As Variant With Target If .Count 1 Then Exit Sub On Error Resume Next If IsError(.Validation.Type) Then Exit Sub On Error GoTo 0 If .Validation.Type = 3 Then If Len(.Value) = 0 Then ci = xlNone Else vMatch = Application.Match(.Value, _ Range(.Validation.Formula1), 0) If IsError(vMatch) Then ci = xlNone Else ci = Range(.Validation.Formula1).Cells(vMatch, _ 1).Interior.ColorIndex End If End If .Interior.ColorIndex = ci End If End With End Sub -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Infoseeker wrote : Then is there a way to create a conditional format for more then 3 colors where a range b47:b58 is red, b58:b69 is blue etc? "keepITcool" wrote: no.. datavalidation just creates a small, basic listbox no customization possible 'in the box'. (unless you're a wizard in APIs and subclassing) -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam Infoseeker wrote : Hi I used validation function to create a drop down list. b3 to b42 uses the same formaula that shows a list from b47:b172. I added 7 or 8 background colors within the b47:b172 cells. is there anyway to get the background color to show when i select them in the b3 to b42 drop downs? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display background color of a cell in a Validated cell
is the list source for your data validation defined via a name object or as a simple "=b47:b212" if a cell is being changed that has datavalidation with dropdown (type 3) then we will attempt: read the range referred to in the list. get the index of the item selected get the colorindex if that item set the colorindex for the cell with the DV please attempt debugging to see what's going wrong. when working it may be a good idea to limit the range where the event handler does its (intended) magic to your actuals by testing and escaping if the intersect(target,Range("b3:b42")) is nothing -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Infoseeker wrote : Hi I think i know what it's trying to do however it didn't work. I have Data Validations on B3:B42 and my range is from =B47:B212 are you trying to display the back ground colors of =B47:B212 when i select them in B3:B42? What am i doing wrong? "keepITcool" wrote: i think you know there isn't... BUT I think following will do what you want: Private Sub Worksheet_Change(ByVal Target As Range) 'if a cell has datavalidation with incell dropdown 'the cell will be colored the same as selected item 'in the list source range Dim ci As Long Dim vMatch As Variant With Target If .Count 1 Then Exit Sub On Error Resume Next If IsError(.Validation.Type) Then Exit Sub On Error GoTo 0 If .Validation.Type = 3 Then If Len(.Value) = 0 Then ci = xlNone Else vMatch = Application.Match(.Value, _ Range(.Validation.Formula1), 0) If IsError(vMatch) Then ci = xlNone Else ci = Range(.Validation.Formula1).Cells(vMatch, _ 1).Interior.ColorIndex End If End If .Interior.ColorIndex = ci End If End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change background color of cell based on vlookup in cell | Excel Discussion (Misc queries) | |||
Moving cell contents to new cell loses background color in old cel | Excel Discussion (Misc queries) | |||
Changing Cell Background Color based on data from another cell | Excel Discussion (Misc queries) | |||
cell background color and cell text color | Excel Worksheet Functions | |||
set the background color of the current cell(active cell) | New Users to Excel |