+ Reply to Thread
Results 1 to 4 of 4

Change drop down list color cell based on range of values

  1. #1
    Registered User
    Join Date
    03-05-2022
    Location
    italy
    MS-Off Ver
    2019
    Posts
    2

    Change drop down list color cell based on range of values

    hello.

    I have an Excel sheet used to record working hours for some projects. I am trying to change the cell color of the dropdownlist cell in column "A" based on the color of a range of cells in BP2:BP14 where I write the names of the projects I work on, where every project has a different color. I'd like the dropdownlist cell, i.e. A9, to have the same color of the project cell (i.e. a = yellow, etc.). Is it possible with conditional formatting or do I need to use VBA?

    Immagine 2022-03-05 115056.jpg

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,540

    Re: Change drop down list color cell based on range of values

    Welcome to the forum.

    For CF, you would need to set up one rule per colour.

    I cannot see the detail in your screenshot - it is better to attach a workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Registered User
    Join Date
    03-05-2022
    Location
    italy
    MS-Off Ver
    2019
    Posts
    2

    Re: Change drop down list color cell based on range of values

    ok thanks. i have done it for one cell (A9) and it works. but now when i copy the conditional formatting created to the other cells of the column, the same rules do not apply. how can i do? do have i to rewrite the cell references for every cell in the column?

    sorry i cannot attach a file. i attached two images more zoomed.

    Immagine 2022-03-05 171710.jpg Immagine 2022-03-05 171738.jpg

  4. #4
    Forum Moderator 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,406

    Re: Change drop down list color cell based on range of values

    I don't know any way in which standard Excel functions can recognise the colour index of a cell. Being able to write a conditional format is dependent on having a value to work with. Almost certainly you'll need VBA

    However you could manually enter the colour index of every colour in BQ alongside the project name in BP. Then use a Conditional format function for each colour

    =VLOOKUP(A1,$BP$1:$BQ$nn,2,FALSE)

    will return the colour index, 3 = red, 10 = green etc.

    As Ali said, upload the workbook. You make it very difficult for us if we can't see the question in context.
    Richard Buttrey

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

+ 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. Change Cell Color based on Drop Down List Selection
    By namealreadtytaken in forum Excel General
    Replies: 5
    Last Post: 01-12-2022, 03:51 AM
  2. [SOLVED] Drop Down Values & Change Cell Color
    By yuenk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-16-2016, 05:43 PM
  3. Drop down list to change cell fill color but not contents
    By dwilhelm07 in forum Excel General
    Replies: 5
    Last Post: 10-18-2016, 03:31 AM
  4. [SOLVED] change fill color of a range of cells based on color of a cell?
    By DarMelNel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-20-2014, 04:48 PM
  5. Change cell color with different drop down values
    By Jonathan9 in forum Excel General
    Replies: 16
    Last Post: 06-20-2013, 06:37 AM
  6. [SOLVED] How to change the drop down list background color by values
    By Tamilarasan B in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-09-2013, 06:53 AM
  7. [SOLVED] Drop Down list range based on two different cell values
    By Jaywuzhere in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-04-2012, 05:22 AM

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