none
[Excel 2k3] Validation List: How to import text & colors present in the selected cells together? RRS feed

  • Question

  • Hi,

    I have 4 cells like this:

    Cell1 Text = Good, Cell1 Color = Green

    Cell2 Text = Excellent, Cell2 Color = Blue

    Cell3 Text = Average, Cell1 Color = Yellow

    Cell4 Text = Bad, Cell4 Color = Red

     

    The Validation List feature create a drop down menu with just the text to select but do not import colors too!

    How can I make it happen, please?

     

    Regards

    Thursday, January 26, 2012 4:45 PM

Answers

  • Ensure that the macros are enabled in the file and then paste this code in the worksheet code area.

    TRIED AND TESTED

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim Rng as Range    

    On Error GoTo Whoa Application.EnableEvents = False '~~> Change it to the relevant range which has the DV List Set Rng = Range("A1") If Not Intersect(Target, Rng) Is Nothing Then Select Case Rng.Value Case "Good": Rng.Interior.ColorIndex = 4 '<~~ GREEN Case "Excellent": Rng.Interior.ColorIndex = 5 '<~~ BLUE Case "Average": Rng.Interior.ColorIndex = 6 '<~~ YELLOW Case "Bad": Rng.Interior.ColorIndex = 3 '<~~ RED Case Else: Rng.Interior.Pattern = xlNone '<~~ REMOVE COLOR if nothing
    End Select End If LetsContinue: Application.EnableEvents = True Exit Sub Whoa: MsgBox Err.Description Resume LetsContinue End Sub


    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.
    Thursday, January 26, 2012 11:29 PM
    Moderator
  • Hi Siddharth Rout, danishani,

     

    I've found more simple solution:

    In Format Menu you choose Conditional Format and then you assign a color for the 3 conditions (then you can have 4 colors if you include the already existing white on the sheet)

     

    Regards

    • Marked as answer by Admin-Dev Monday, January 30, 2012 8:18 PM
    Monday, January 30, 2012 8:18 PM

All replies

  • Ensure that the macros are enabled in the file and then paste this code in the worksheet code area.

    TRIED AND TESTED

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim Rng as Range    

    On Error GoTo Whoa Application.EnableEvents = False '~~> Change it to the relevant range which has the DV List Set Rng = Range("A1") If Not Intersect(Target, Rng) Is Nothing Then Select Case Rng.Value Case "Good": Rng.Interior.ColorIndex = 4 '<~~ GREEN Case "Excellent": Rng.Interior.ColorIndex = 5 '<~~ BLUE Case "Average": Rng.Interior.ColorIndex = 6 '<~~ YELLOW Case "Bad": Rng.Interior.ColorIndex = 3 '<~~ RED Case Else: Rng.Interior.Pattern = xlNone '<~~ REMOVE COLOR if nothing
    End Select End If LetsContinue: Application.EnableEvents = True Exit Sub Whoa: MsgBox Err.Description Resume LetsContinue End Sub


    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.
    Thursday, January 26, 2012 11:29 PM
    Moderator
  • Hi Siddharth Rout, danishani,

     

    I've found more simple solution:

    In Format Menu you choose Conditional Format and then you assign a color for the 3 conditions (then you can have 4 colors if you include the already existing white on the sheet)

     

    Regards

    • Marked as answer by Admin-Dev Monday, January 30, 2012 8:18 PM
    Monday, January 30, 2012 8:18 PM