Data Validation from lists - values + formats!

[WatfordKev]

I want to select values from a dropdown based on :

• lists of data on another worksheet
• there may be more than 3 values (so Cond Formatting is not an option)
• I want the formatting from the list cell as well as the values

I know I know I want jam on it too!!

 

[GlennUK]

What kind of formatting? How are your VBA skills?

 

[WatfordKev]

Hi Glenn

apologies - should have been more clear - just the background colour actually! My VBA skills are novice level, I would say... I do have a coding background tho...

 

[GlennUK]

Look at this, and adapt it for your needs ( in fact I think it'll be simpler ). Use defined names for your lists in the reference sheet.

http://www.mrexcel.com/forum/showthread.php?t=555544

 

[WatfordKev]

Dear Glenn

OK I am going to look really dopey now... I have altered others' Macros and code, but I don't know how to attach that code (it appears to be exactly what I want) to my dropdown. Sorry to be a pain!

 

[GlennUK]

You put your code in the sheet code area ... Right-click the sheet tab and choose View Code from the shortcut menu, and put your code there.

 

[WatfordKev]

Thanks for this, I've now got it working thanks to you.

I have it working for the font, but the background is not changing. I notice the property on the code is 'Font.ColorIndex' - which doesnt change the background. I tried substituting 'Interior' for 'Font' with no luck - I was wondering if there was a parameter that would format the background too? (I can't determine one!)

 

[GlennUK]

It is indeed Interior. Show your code ... you may have made some other minor error.

 

[WatfordKev]

Hi Glenn

thanks again - this is what I've attempted ("438 - Object doesn't support this method or property")

Private Sub Worksheet_Change(ByVal Target As Range)
If Not IsError(Application.Match(Target.Value, [sourcelist], 0)) Then
Target.Interior.ColorIndex = xlAutomatic
Target.Characters(1, 1).Interior.ColorIndex = Range("SourceList").Cells(Application.Match(Target.Value, [sourcelist], 0)).Characters(1, 1).Interior.ColorIndex
End If
End Sub

 

[GlennUK]

Ah, your using almost exactly the same code as for testing the first character of the cell contents ... yours will be muich simpler, similar to this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not IsError(Application.Match(Target.Value, [sourcelist], 0)) Then
   Target.Interior.ColorIndex = Range("SourceList").Cells(Application.Match(Target.Value, [sourcelist], 0)).Interior.ColorIndex
End If
End Sub