+ Reply to Thread
Results 1 to 9 of 9

How to include the color of drop down list in Data Validation

  1. #1
    Registered User
    Join Date
    02-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    How to include the color of drop down list in Data Validation

    I have a list of Activity Definitions B4:B28 and I have already added a color to each cell. I want that color to transfer to the time log cells Data Validation list on the Activity Sheet. Can that be done? Or do I have to use the conditional formatting and "restate" all of the colors? It seems like it should pull the color from the list of cells I have chosen.

    Thank you for any help!
    Barbara

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to include the color of drop down list in Data Validation

    From your description and without seeing the workbook you'll have to use Cond. Formatting to 'restate' all the colours.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    02-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: How to include the color of drop down list in Data Validation

    Ok, well, that is disappointing! But thank you for your quick reply!!

  4. #4
    Registered User
    Join Date
    12-05-2019
    Location
    usa
    MS-Off Ver
    2007
    Posts
    7

    Re: How to include the color of drop down list in Data Validation

    You may not have to restate the CF rules.
    In the Applies To box in CF, add a comma and the range of cells the Data Validation applies to,
    so you would have something like:

    =$B$4 , $X$2:$X$20

  5. #5
    Registered User
    Join Date
    02-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: How to include the color of drop down list in Data Validation

    Thank you Misanthrope! I am not able to get that to work, but that may be my lack of knowledge. Thank you for your response!

  6. #6
    Registered User
    Join Date
    12-05-2019
    Location
    usa
    MS-Off Ver
    2007
    Posts
    7

    Re: How to include the color of drop down list in Data Validation

    Keep getting error message when I try to reply

  7. #7
    Registered User
    Join Date
    12-05-2019
    Location
    usa
    MS-Off Ver
    2007
    Posts
    7

    Re: How to include the color of drop down list in Data Validation

    Keep getting an error message when I try to send a reply.
    Can't post the error message, because I get an error message when I try to send it.

  8. #8
    Registered User
    Join Date
    12-05-2019
    Location
    usa
    MS-Off Ver
    2007
    Posts
    7

    Re: How to include the color of drop down list in Data Validation

    I have a list of Activity Definitions B4:B28 and I have already added a color to each cell

    In cell B4 you have the word Widget, which you have colored Blue, using Conditional Formatting
    In cell B5, you have the word Sprocket, which you have colored Green, using Conditional Formatting
    In cell B6 you have the word Flange, which you have colored Yellow, using Conditional Formatting
    Etc. etc.

    I want that color to transfer to the time log cells Data Validation list.
    Do not know what cells you have placed the Data Validation in so I will use the cell range: X4 thru X20


    Place cursor on cell B4 and select Conditional Formatting from the ribbon.
    Next, select Manage Rules from the CF window that pops up, the bottom selection.
    In the Rules Manager pop up window, there is a box that reads, Applies To and it should show =$B$4
    Place your cursor at the end of the formula in the Applies To box and enter a comma,
    then the range of cells that have Data Validation, IE X4 thru X20
    so the Applies To box now appears as: =$B$4,$X$4:$X$20
    Click apply then OK

    Now when you use the drop down in cell X4,
    When you select the word Widget, which is in a blue cell,
    Cell X4 will have the word Widget with cell color of blue.

    When you use the drop down in cell X5,
    When you select the word Flange, which is in a yellow cell,
    Cell X5 will have the word Flange with cell color of yellow.

  9. #9
    Registered User
    Join Date
    12-05-2019
    Location
    usa
    MS-Off Ver
    2007
    Posts
    7

    Re: How to include the color of drop down list in Data Validation

    The error message apparently has to do with trying to use the Italicize function on sentences.
    Here is the error message, if it let's me post it.

    Access Denied - Sucuri Website Firewall

    If you are the site owner (or you manage this site), please whitelist your IP or if you think this block is an error please open a support ticket and make sure to include the block details (displayed in the box below), so we can assist you in troubleshooting the issue.
    Block details:
    Your IP: 104.228.229.137
    URL: [ u r l ] w w w excel forum com / newreply.php?do=postreply&t=1302728[/url]
    Your Browser: Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:56.0) Gecko/20100101 Firefox/56.0 Waterfox/56.3
    Block ID: SQLi17
    Block reason: SQL injection was detected and blocked.
    Time: 2020-01-16 14:15:43
    Server ID: 14015

    Won't let me post links, so have tried to modify the message a bit.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 07-30-2019, 03:26 PM
  2. [SOLVED] Color coding data validation drop list
    By Jasikanicolepi in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-21-2017, 01:34 AM
  3. Replies: 3
    Last Post: 07-05-2015, 12:45 AM
  4. Data validation List: A macro to initiate drop down list?
    By dchubbock in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-11-2013, 08:12 PM
  5. Replies: 1
    Last Post: 09-05-2012, 11:39 AM
  6. Formula to include spaces in linked data validation list
    By hbomb1927 in forum Excel General
    Replies: 2
    Last Post: 10-25-2011, 07:01 PM
  7. Replies: 0
    Last Post: 08-08-2011, 01:22 PM

Tags for this Thread

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