Adding a functionality to change the color of ASINS

[rahulpandita]

I have 2 sheets – Data & Required Format

I had to color the ASINS at every change in Coll_Var with Orange & Blue. While coloring the internal child asins with lighter and darker shades of the same colour as the Parent and i have used a macro to change the color of the ASINS.

I am using excel 2007. Now i need a flexible code to change the colur of ASINS according to the user's need.

[macropod]

What is ASINS? And please show us the code you're already using - without seeing it, no-one will be able to tell you what changes need to be made. When posting the code, please use the VBA code tags.

[rahulpandita]

ASINS means rows n i have attached an excel file containing the code.

[macropod]

Hi rahulpandita,

Your current workbook has two hard-coded themes (BaseColor1 & BaseColor2). What do you want to do?

Do you want the user to be able to choose from more themes for BaseColor1 & BaseColor2 (there are 18 index members in all - or you could give the user of choice of RGB values (16,777,216 possibilities). Either of these will require a userform to implement.

Do you want to workbook to automatically apply more themes (eg BaseColor1, BaseColor2, BaseColor3 & BaseColor4)? This would require only a trivial code change.

Also, is there any particular reason for specifying:
.TintAndShade = 0.799981688894314
It seems to me you'd get pretty much the same effect with:
.TintAndShade = 0.8

[rahulpandita]

I want a user form to implement giving user the opportunity to choose color from the 18 index members but i dont know how to implement it. I have a excel file in which a user form is implement in the addins tab on the top. Can u help me in building the same kind of interface for user form in this file.

I am attaching that template having the user form implemented..

[macropod]

Hi rahulpandita,

Attached is an update to your original workbook, with a very basic useform for selecting the theme colours. As it turns out, only 10 can be used this way. I'll leave you to do any tidying up the layout, adding captions, etc.

As coded, the form doesn't retrieve whatever theme values might already be in use in the file. If you want to be able to do that, you will need to create some custom properties and write the values to them so that they can be retrieved. I'll leave you to do that, if that's what you want. Aside from that, the userform will re-initialize with the current them settings once you've picked a pair.

[rahulpandita]

Thanks a lot... I have seen the attached file.. i will now try to make some changes according to the need.