+ Reply to Thread
Results 1 to 14 of 14

Conditional Formatting for highlighting cells with dropdown values.

  1. #1
    Registered User
    Join Date
    03-03-2014
    Location
    Chennai
    MS-Off Ver
    Excel 2007
    Posts
    8

    Conditional Formatting for highlighting cells with dropdown values.

    Hi,

    I want to highlight all the cells in a worksheet that have dropdown lists in blue color. Not sure how to do it.

  2. #2
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Conditional Formatting for highlighting cells with dropdown values.

    Hi S@2014

    You ask for conditional formatting - why not use good old formatting and use "Fill Colour" blue?

    Puzzled

    Regards
    Alastair

  3. #3
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Conditional Formatting for highlighting cells with dropdown values.

    Oops - pressed send too soon.

    Re-reading this I now believe that you want the whole list to be blue rather than (as I originally thought) just the visible cell.

    Still puzzled

    Alastair

  4. #4
    Registered User
    Join Date
    03-03-2014
    Location
    Chennai
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Conditional Formatting for highlighting cells with dropdown values.

    Hi,

    Currently, i have a worksheet with a lot of cells with dropdown. I need to color all these dropdown lists with blue. Doing it manually is difficult as i need to go through each and every cell. Wanted to know if there is any macro to fill cells with dropdown instantly.

  5. #5
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Conditional Formatting for highlighting cells with dropdown values.

    Hi S@2014

    I'm still confused. Is it just the cell that you want coloured, or is it the background of the dropdown data?

    Regards
    Alastair

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

    Re: Conditional Formatting for highlighting cells with dropdown values.

    You may be able to use a vlookup() in the CF for the color, do you have a list somewhere of data for the drop-downs?
    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

  7. #7
    Registered User
    Join Date
    03-03-2014
    Location
    Chennai
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Conditional Formatting for highlighting cells with dropdown values.

    just want to color the cells in blue.

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

    Re: Conditional Formatting for highlighting cells with dropdown values.

    OK and I am suggesting 1 way that may do that for you. Can you upload a sample workbook?

  9. #9
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Conditional Formatting for highlighting cells with dropdown values.

    Hi S@2014

    I go back to my original suggestion. Colour the first cell blue. Go to each other cell and press F4.

    Regards
    Alastair

  10. #10
    Registered User
    Join Date
    03-03-2014
    Location
    Chennai
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Conditional Formatting for highlighting cells with dropdown values.

    I have uploaded a sample file. It has many drop-down lists. I need to click each cell to see if it has a dropdown and then color it. Need to know a workaround.
    Attached Files Attached Files

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

    Re: Conditional Formatting for highlighting cells with dropdown values.

    If that is what all your dropdowns look like....
    1. highlight the range you want to apply the conditional formatting to
    2. on the home tab, styles, select CF
    3. select new rule, select format only cells that contain
    4. enter Applicable format fill blue

  12. #12
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    604

    Re: Conditional Formatting for highlighting cells with dropdown values.

    Click the button attached.

    It will color ALL cells with data validation in Blue, NOT just the dropdown lists.


    Please Login or Register  to view this content.
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    604

    Re: Conditional Formatting for highlighting cells with dropdown values.

    This one is even better...

    It looks for DropDown Lists specifically, and highlights them in "BLUE". Other types of data validation are ignored.

    Notes:
    1) The gray cells are dropdown lists
    2) The yellow cells have a different type of data validation
    3) Only the dropdown lists are highlighted BLUE


    Please Login or Register  to view this content.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    03-03-2014
    Location
    Chennai
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Conditional Formatting for highlighting cells with dropdown values.

    Thanks, it's working great.

+ 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. Highlighting the values using conditional formatting in vba
    By satpalbhullar87 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-19-2013, 11:08 PM
  2. Conditional formatting highlighting multiple values
    By upperguy in forum Excel General
    Replies: 3
    Last Post: 04-22-2013, 12:53 PM
  3. Replies: 3
    Last Post: 06-12-2012, 04:52 PM
  4. highlighting the cells using conditional formatting
    By darkhangelsk in forum Excel General
    Replies: 2
    Last Post: 08-19-2009, 10:46 PM
  5. conditional formatting/highlighting cells
    By rae1222 in forum Excel General
    Replies: 8
    Last Post: 01-30-2008, 05:54 PM

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