I have a column of cells that are set up with a pull down list for a new budget that I'm creating. The pull down list consists of four options of different accounts, and when an account is selected, a color fill is generated to identify that cell appropriately. I watched your video about using a macro to generate a code for the different colors used in a column and tried to generate the same thing. Unfortunately, when I run the Macro provided in your video, even though the colors in the cells are different, it generates the exact same code number in all of the cells coded. Do I have to manually apply color to all the cells or can I keep the conditional formatting and the pull down menus? Any help would be gratly appreciated. Thanks so much.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Using a Macro to generate code for color when conditional formatting is already in use.
1
Answers
0
This is actually quite interesting and I hadn't thought about it! I'll have to update the tutorial as well or create a new one just for this scenario!
Try this code, slightly changed from the original macro to get the color of a cell:
Sub ListColors_ConditionalFormatting()
For Each c In Selection
c.Offset(0, 1).Value = c.DisplayFormat.Interior.Color
Next c
End Sub
Discussion
Hi Don.
I had a look at the excelent video and would like to suggest you could do an advanced version. You could turn your macro procedure into a function which woud accept a cell as input. It could then test for either DispalyFormat or interior color. Being a function it can be used in formula. Unfortunatly I am not at work and don't have Excel at home so I am unable to test this but I suspect that if you point the function at its own cell, with th sumifs, asyou changes its colour the vlaues should change. As an aside, if youo make an advanced version you might like to also include named ranges in you fromulas.
Just a few ideas. keep up the good work
Steve
I had a look at the excelent video and would like to suggest you could do an advanced version. You could turn your macro procedure into a function which woud accept a cell as input. It could then test for either DispalyFormat or interior color. Being a function it can be used in formula. Unfortunatly I am not at work and don't have Excel at home so I am unable to test this but I suspect that if you point the function at its own cell, with th sumifs, asyou changes its colour the vlaues should change. As an aside, if youo make an advanced version you might like to also include named ranges in you fromulas.
Just a few ideas. keep up the good work
Steve
k1w1sm (rep: 197) Jun 3, '18 at 10:31 pm
I've done something similar to this but probably not as robust. I'll definitely look into it and maybe can upload a macro for that on here. By the way, if you have anything you'd like to submit, you can click the Submit a Tip button in the upper-left corner of the screen and I'm happy to look over it for inclusion on the site and the weekly newsletter!
I'd really like to build-up a bigger and more useful macro repository so people can get these things more easily and for free.
I'd really like to build-up a bigger and more useful macro repository so people can get these things more easily and for free.
don (rep: 1989) Jun 8, '18 at 7:11 am
Hi Don,
I have been receiving the weekly news letter for some time. I have only just started visiting the site. A lot of what I am currently doing invloves ADODB connections to Oracel databases. Possably a bit specialised or not lending itself to a newsletter item. I will need to check what is currently available, on this site, to see if there is anything I can add. It seems to quite comprehensive already.
Steve
I have been receiving the weekly news letter for some time. I have only just started visiting the site. A lot of what I am currently doing invloves ADODB connections to Oracel databases. Possably a bit specialised or not lending itself to a newsletter item. I will need to check what is currently available, on this site, to see if there is anything I can add. It seems to quite comprehensive already.
Steve
k1w1sm (rep: 197) Jun 9, '18 at 6:40 am
Thanks Steve! Honestly, you can upload anything that teaches how to do anything in Excel. If you want to upload specialized information about making ADODB connections and Oracle db's, I can create a new category for those tutorials so you can upload them and links to them would certainly be included in the newsletter :). I certainly don't know how to use all of Excel's vast toolset myself haha.
don (rep: 1989) Jun 16, '18 at 2:03 am
0
I have attached an excel workbook which demonstrates changing the colours of a cell in a data validation drop down matching the colour of the value in the validation range.
It is a bit simple in that changing the colour on the colours sheet does not update the dropdown sheet but the next use will use the new colour.
Note this uses named ranges in an attempt to make it more robust in that you might be able to move stuff around without it breaking
Good luck
Discussion
Sorry for the delay, just saw this. Correct me if I'm wrong, but the file you sent is different than what you mentioned in your other posts, correct? Because it seems like you made your own kind of custom conditional formatting code, instead of using the standard conditional formatting setup. Maybe I'm missing something, but I'm curious why you chose to do it that way instead of just using custom conditional formatting - was it so you can manage the conditional formatting from within another worksheet?
don (rep: 1989) Jun 8, '18 at 7:09 am
Hi Don
Not sure which other post you are refering to but I think the file I sent shows conditional formating replacement.
It is just a concept of what I have written, for our own use, to demonstrat the technique.
I started using conditional formatting in a previous Excel version and ran out conditions. I believe more recent versions have increased this limit.
The speadsheet is used to manage sortware testing and has a select case for establishing the colour for a row.
primarly the status or the test is dominant except where the status is pending
The pending colour can be superseeded by a product type. There is no limit to the product type the macro loocs for the macthing type and grabs that cells colour, tint and font colour. As there may be more than one person testing an application then where a name is enterd in the tester column then that will be the dominate colour. Once again there is no limit to the number of testers. I wrote this 11 years ago and as I commented previously there is about 400 lines of code in this I suspect I could do this better now but it isn't broke so no need to fix it. As I say I have not used conditional formatting for some time but what I have written allows users and products to be added and they can choose the colour they want or change it with no code change at all.
Not sure which other post you are refering to but I think the file I sent shows conditional formating replacement.
It is just a concept of what I have written, for our own use, to demonstrat the technique.
I started using conditional formatting in a previous Excel version and ran out conditions. I believe more recent versions have increased this limit.
The speadsheet is used to manage sortware testing and has a select case for establishing the colour for a row.
primarly the status or the test is dominant except where the status is pending
The pending colour can be superseeded by a product type. There is no limit to the product type the macro loocs for the macthing type and grabs that cells colour, tint and font colour. As there may be more than one person testing an application then where a name is enterd in the tester column then that will be the dominate colour. Once again there is no limit to the number of testers. I wrote this 11 years ago and as I commented previously there is about 400 lines of code in this I suspect I could do this better now but it isn't broke so no need to fix it. As I say I have not used conditional formatting for some time but what I have written allows users and products to be added and they can choose the colour they want or change it with no code change at all.
k1w1sm (rep: 197) Jun 9, '18 at 6:21 am
Nice! I gotta say I like it! And it makes sense when you describe the use-case. Honestly, I sometimes don't understand why Excel ever put some of the limitations it did on its software. I would think they should have just allowed you to have as many conditions/cells/etc. as your hardware can handle, like a proper database tends to, practically, do.
don (rep: 1989) Jun 16, '18 at 2:09 am
Thank you for the kind comments. Since starting this discussion (and viewing other questions around conditional formating) I have revisited conditional formatting. I consider myself to have some ability with excel but I am still yet to master conditional formatting. Mind you it does look very interesting with some of the progressive shading. I will come back here if ever I find a use for it.
k1w1sm (rep: 197) Jun 16, '18 at 6:42 am