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?

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?

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?

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?

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?

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?

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?

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?

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?

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