List box that changes fill color

sarahjanebaird

New Member
Joined
Jan 8, 2020
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain color fill the date but I still need to be able to see the date in the cell. I want to use a drop down box because the people who will be using this cannot be fully trusted to use the chosen colors if they have to go through the fill list (I saw their last year list and it is a mess of colors)

Please help! I've uploaded an image hopefully showing better what I mean!
 

Attachments

  • Untitled.png
    Untitled.png
    62 KB · Views: 33

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Welcome to the forum

Is this what you want to happen ?

1. User clicks on cell for Monday 20th January
2. Dropdown appears for user to select ONE of these values:
< 40 %
40 - 49 %
50 - 59 %
60 - 69 %
70 - 79 %
80 - 89 %
90 - 99 %
100%
3. If user select 100% the cell is filled RED , if user selects <70-79% the cell is filled GREEN etc

If you want something different, please detail step by step
thank you
 
Upvote 0
Welcome to the forum

Is this what you want to happen ?

1. User clicks on cell for Monday 20th January
2. Dropdown appears for user to select ONE of these values:
< 40 %
40 - 49 %
50 - 59 %
60 - 69 %
70 - 79 %
80 - 89 %
90 - 99 %
100%
3. If user select 100% the cell is filled RED , if user selects <70-79% the cell is filled GREEN etc

If you want something different, please detail step by step
thank you

That is exactly what I want! Is it possible to just change the fill color without the cell also changing to say "100%" etc. ?
 
Upvote 0
Yes it can be done but requires some VBA
Have you used VBA before ?
 
Upvote 0
1. OK first challenge is to make the Developer Tab visible
Right-click on the Ribbon \ click on Customize Ribbon \ in pane on right ... check the box for Developer Tab and click OK at bottom of screen
Hopefully the Developer tab is available now

2. Next open a NEW workbook to play in!! We will test everything here

3. Copy the calendar to sheet 1

4. Place these values in range AZ1 to AZ9 (I suggest you copy and paste each one individually to make sure EVERYTHING is identical)
Choose
< 40 %
40 - 49 %
50 - 59 %
60 - 69 %
70 - 79 %
80 - 89 %
90 - 99 %
100%


4. Click Developer Tab \ Insert \ Active-X Controls \ click on the ListBox icon \ click anywhere on sheet and drag it to a sensible size

5. The workbook should now be in "Design Mode" (that button on Developer tab will be darker than rest of tab)
- if it isn't then click on that button (it is a toggle between the 2 states)

6. Next to that button is the Properties button which should open the ListBox1 Properties
(if you see a different window, then click on the ListBox and the window will change)
To right of LIstFillRange enter the range containing your values AZ1:AZ9 and then click on "Design Button" to put Excel back to normal mode

ListBoxProperties.jpg


7. Test to see if the listbox is working . Should look like this when you click on it
Listbox.jpg



I expect to post some code within 1 hour
 
Upvote 0
1. OK first challenge is to make the Developer Tab visible
Right-click on the Ribbon \ click on Customize Ribbon \ in pane on right ... check the box for Developer Tab and click OK at bottom of screen
Hopefully the Developer tab is available now

2. Next open a NEW workbook to play in!! We will test everything here

3. Copy the calendar to sheet 1

4. Place these values in range AZ1 to AZ9 (I suggest you copy and paste each one individually to make sure EVERYTHING is identical)
Choose
< 40 %
40 - 49 %
50 - 59 %
60 - 69 %
70 - 79 %
80 - 89 %
90 - 99 %
100%


4. Click Developer Tab \ Insert \ Active-X Controls \ click on the ListBox icon \ click anywhere on sheet and drag it to a sensible size

5. The workbook should now be in "Design Mode" (that button on Developer tab will be darker than rest of tab)
- if it isn't then click on that button (it is a toggle between the 2 states)

6. Next to that button is the Properties button which should open the ListBox1 Properties
(if you see a different window, then click on the ListBox and the window will change)
To right of LIstFillRange enter the range containing your values AZ1:AZ9 and then click on "Design Button" to put Excel back to normal mode

View attachment 3504

7. Test to see if the listbox is working . Should look like this when you click on it
View attachment 3505


I expect to post some code within 1 hour
So far so good! You are a genius and thank you SO MUCH for your help! I really appreciate this.
 
Upvote 0
I need something from you
One way to tell VBA which colour to use is to point it at a cell containing the correct colour fill
There are 8 colours that you want to use
I need ONE cell reference for each one

Plesae reply with the 8 cell references
- I can then point vBA at the appropriate cell to match what user selects
 
Upvote 0
< 40 % - F2
40 - 49 % - J2
50 - 59 % - N2
60 - 69 % - R2
70 - 79 % - V2
80 - 89 % - Z2
90 - 99 % - AD2
100%
- AH2

Is that what you mean?
 
Upvote 0
Yes - that is what I mean

I also want the range of cells containing the calendar
What is the reference of top left cell in January and bottom right in December (I assume Sept to Dec are below May to Aug)
(ie the first empty cell in January and last empty cell in December)
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,720
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top