+ Reply to Thread
Results 1 to 5 of 5

automatic color change in cells using a drop down list

  1. #1
    kennethwt
    Guest

    automatic color change in cells using a drop down list

    How do I get the colors to change automatically when I use a drop down list.
    Per say here is what I am trying to do

    If a certain "word" is used from the drop down list I want the background of
    that cell to change its color to "green." And if later I change the "word" to
    another from the drop down list, it will change it's color to a specified
    color.

    The drop down list that I use was created from cells that have the colors
    already in the "word", but I do not know how to make the list show the colors
    so it puts the word & color automatically in the drop down list to the cell
    with the drop down (if that makes any sense).

    If there is a way, please spell it out simple enough for me to understand,
    as I don't know fully the capabilities of formulas or vba.

  2. #2
    Registered User
    Join Date
    01-19-2005
    Posts
    2
    Under Format, Conditional Formatting, choose 'cell value is', 'equal to', ="word". In the format box, change it to the color you wish for 'word', then click add at the bottom to enter similar information for each word in your your list. Use format painter to copy the formatting to each cell you wish this to happen with.

    Hope this helps.

  3. #3
    Gord Dibben
    Guest

    Re: automatic color change in cells using a drop down list

    ken

    You could use Conditional Formatting to color the cell based on the "word"
    entered.

    However this gives you 4 choices only, including the default "no color".

    For more you would need VBA event code.

    Example below. Adjust range and A, B, C etc. to suit.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Num As Long
    Dim rng As Range
    Dim vRngInput As Variant
    Set vRngInput = Intersect(Target, Range("A1"))
    If vRngInput Is Nothing Then Exit Sub
    On Error GoTo endit
    Application.EnableEvents = False
    For Each rng In vRngInput
    'Determine the color
    Select Case rng.Value
    Case Is = "A": Num = 10 'green
    Case Is = "B": Num = 1 'black
    Case Is = "C": Num = 5 'blue
    Case Is = "D": Num = 7 'magenta
    Case Is = "E": Num = 46 'orange
    Case Is = "F": Num = 3 'red
    End Select
    'Apply the color
    rng.Interior.ColorIndex = Num
    Next rng
    endit:
    Application.EnableEvents = True
    End Sub

    This code wopuld be copy/pasted to a worksheet module.

    Right-click on the sheet tab and "View Code" to open the module.


    Gord Dibben Excel MVP

    On Thu, 20 Jan 2005 20:53:01 -0800, kennethwt
    <kennethwt@discussions.microsoft.com> wrote:

    >How do I get the colors to change automatically when I use a drop down list.
    >Per say here is what I am trying to do
    >
    >If a certain "word" is used from the drop down list I want the background of
    >that cell to change its color to "green." And if later I change the "word" to
    >another from the drop down list, it will change it's color to a specified
    >color.
    >
    >The drop down list that I use was created from cells that have the colors
    >already in the "word", but I do not know how to make the list show the colors
    >so it puts the word & color automatically in the drop down list to the cell
    >with the drop down (if that makes any sense).
    >
    >If there is a way, please spell it out simple enough for me to understand,
    >as I don't know fully the capabilities of formulas or vba.



  4. #4
    Registered User
    Join Date
    12-07-2011
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: automatic color change in cells using a drop down list

    Could a live worksheet be uploaded with the said VBA Code

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    45,703

    Re: automatic color change in cells using a drop down list

    hmmangnani this thread is over 8 years old, I doubt they are still watching this
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1