List box that changes fill color

sarahjanebaird

New Member
Joined
Jan 8, 2020
Messages
15
Office Version
365
Platform
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

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,660
Office Version
365
Platform
Windows
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
 

sarahjanebaird

New Member
Joined
Jan 8, 2020
Messages
15
Office Version
365
Platform
Windows
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. ?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,660
Office Version
365
Platform
Windows
Yes it can be done but requires some VBA
Have you used VBA before ?
 

sarahjanebaird

New Member
Joined
Jan 8, 2020
Messages
15
Office Version
365
Platform
Windows
Yes it can be done but requires some VBA
Have you used VBA before ?
I have not, I am a fairly quick learner though if it's possible to teach!
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,660
Office Version
365
Platform
Windows
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
 

sarahjanebaird

New Member
Joined
Jan 8, 2020
Messages
15
Office Version
365
Platform
Windows
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.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,660
Office Version
365
Platform
Windows
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
 

sarahjanebaird

New Member
Joined
Jan 8, 2020
Messages
15
Office Version
365
Platform
Windows
< 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?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,660
Office Version
365
Platform
Windows
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)
 

Forum statistics

Threads
1,081,959
Messages
5,362,403
Members
400,673
Latest member
RobBro1987

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top