How to change the drop down list background color by values

**Tamilarasan B** · 01-07-2013, 04:38 AM

Hi Experts,

I need to produce one report for system performance for that i inserted drop down list(ActiveX)with three values OK,Corrective action taken,Outage found.

I feel that if i select OK the background of Drop down list should be in Green,Corrective action should be in yellow,Outage found should be in Red then it would be a effective report.

I don't know the Macro or VB stuffs.

Please any one guide me how to achieve this or Please give the exact Macro.

Thanks in advance.

Regards,
Tamil

**barryleajo** · 01-07-2013, 09:58 AM

Hi Tamilarasan B
Welcome to the forum.
Basically the approach on a User Form is to:
1. Create/size a ComboBox
2. Set its BackStyle property to transparent
3. Create a Label the same size and placed directly behind the ComboBox
4. Populate the ComboBox list
5. Wite code to set the BackColor property of the Label depending on the Value of the ComboBox

See attached which should get you going.

barry

Tamilasaran070113.xlsm

**Tamilarasan B** · 01-07-2013, 11:49 AM

Dear Barry,

Thanks a lot for immediate response.It is very useful to solve the thread however is it possible do it without the buttons for drop down list?Please find attached screen shot.I did changed the background color manually based on the values.
Untitled.png
Thanks a lot once again for your valuable answer.

Regards,
Tamilarasan B

**barryleajo** · 01-07-2013, 04:15 PM

Hello Tamil
Please see the attached for a simpler solution for you.
This uses the "built-in" features of Excel conditional formatting and data validation - no coding required.
Simply click in the cells to select.
Hope this helps you further.
barry

Tamilasaran070113v2.xlsm

**Tamilarasan B** · 01-08-2013, 02:58 AM

Dear berry,

This is what i want.Really useful for me Thanks a lot dude.Please tell how you did this by step by step for my learning purpose.

Once again Thanks.

Regards,
Tamil

**Tamilarasan B** · 01-08-2013, 02:59 AM

Dear berry,

This is what i want.Really useful for me Thanks a lot dude.Please tell how you did this by step by step for my learning purpose.

Once again Thanks.

Regards,
Tamil

**barryleajo** · 01-08-2013, 04:52 PM

Hello Tamil
I am glad you are sorted.
For your learning:
1. Select one of the coloured cells in Excel
2. Go to the Home tab in Excel menu
3. Select Conditional Formatting (in the Styles group)
4. Select Manage Rules option
You will see the rules, formatting and cells to which they are applied
THEN
5. Select one of the coloured cells in Excel
6. Go to the Data tab in Excel menu
7. Select Data Validation (in the Data Tools group)
8. Select Data Validation option
You will see how the pull-down lists are populated.

Hope this helps.
barry

**Tamilarasan B** · 01-09-2013, 06:53 AM

Dear Berry,

It is very clear explanation.I got it.Thanks a lot friend.

Wish you happy New year.

I could not give reward to your post it says "you must spread some reputation..." like this

sorry friend.

Once again Thanks a lot!
Regards,
Tamil