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?

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?

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

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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?
>>

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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
>> 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?
>>>
>
>

....actually, I just realised that it doean't answer the quaetion.


"Patrick Molloy" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
>> 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?
>>>
>
>

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

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

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