+ 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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,917

    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" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them 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 - 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: 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

    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.

+ 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. 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