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?