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

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,361
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
6,361
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
6,361
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
6,361
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
6,361
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,102,599
Messages
5,487,782
Members
407,610
Latest member
bellakim00

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top