Changing colour on changing a Data Validation List added with VBA

**e4excel** · 08-12-2011, 04:12 PM

Changing colour on changing a Data Validation List with VBA

Dear Forum,

As per the Title, I am Adding Data Validation using a Macro but I want to also change the colour on selecting the Items in the desired column F.

Please Login or Register  to view this content.

I am getting 90 filled rows instead of 45 in the column F and 45 in Column G when I am running the same code again for the second column G

I want to add Data Validation Items such as :1 Fixed Cost which should be in the Red Colour i.e. Colour Index = 3
2 Variable Cost which should be in the Blue Colour i.e. Colour Index = - 4165632

The Text should have the below Formatting
Horizontal Alignment= Left
Font = "Book Antiqua"
Font Size = 13

I want to get the colour change immediately on changing the Data Validation...

Warm Regards
e4excel

**davesexcel** · 08-12-2011, 08:23 PM

You need to place code in the worksheet_change event to change the color of the cell.
The row issue is hard to describe as you have not supplied code that shows what is going on.
Maybe supply a very simple workbook showing what you are asking.

**e4excel** · 08-13-2011, 07:31 AM

Ok as you say DavesExcel,

I will be uploading an attachment...
I think I require some tweak in the Data Validation as well as something going wrong somewhere, as I have taken this code from the net I am nost sure of repairing the same..

So, pleae help me over two things :
1. Getting the DataValidation through a Macro and also till only the last filled row in COlumn E...

2. Then I need the colours to change on changing the values from the list..

I know I can do it simply through the regular Data Validation and also can change colours through conditional formatting however, I want to learn VBA....

Please take the range from the Column J i.e J2:J3 for the DV in Col F &
and the range from the Column K i.e K2:K4 for the DV in Col G..

1 Fixed Cost which should be in the Red Colour i.e. Colour Index = 3
2 Variable Cost which should be in the Blue Colour i.e. Colour Index = - 4165632

The Text should have the below Formatting
Horizontal Alignment= Left
Font = "Book Antiqua"
Font Size = 13

I want to get the colour change immediately on changing the Data Validation...

Warm Regards
e4excel

**e4excel** · 08-13-2011, 10:57 AM

I found an useful link to change the Font Colour..

http://www.excelforum.com/excel-prog...using-vba.html

**e4excel** · 08-13-2011, 11:17 AM

Please help me with the Data Validation as that is happening erroneously for 90 rows instead of just 45..

For reference my trial and error modified code:

Please Login or Register  to view this content.

**e4excel** · 08-16-2011, 02:18 AM

Bump No Response

**e4excel** · 08-16-2011, 08:15 AM

I think I found it..!

Please Login or Register  to view this content.

Changing colour on changing a Data Validation List added with VBA

LinkBack

Thread Tools

Rate This Thread

Display

Changing colour on changing a Data Validation List added with VBA

Re: Changing colour on changing a Data Validation List with VBA

Re: Changing colour on changing a Data Validation List with VBA

Re: Changing colour on changing a Data Validation List with VBA

Re: Changing colour on changing a Data Validation List with VBA

Bump No Response

Re: Changing colour on changing a Data Validation List added with VBA

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions