GClutterbuck
asked on
Automatic formatting based on more than 4 conditions
Hi,
I wish to apply formatting to the entire row, when a user selects an entry from a list of values (data validation). The list contains 12 different choices. How can I get the font colour for the entire row to change automatically when the user selects a choice from the list.
I am aware that conditional formatting would normally be the best option, but I don't see how this can be applied as I have so many alternative choices.
Any suggestions would be greatfully received. Thank you.
I wish to apply formatting to the entire row, when a user selects an entry from a list of values (data validation). The list contains 12 different choices. How can I get the font colour for the entire row to change automatically when the user selects a choice from the list.
I am aware that conditional formatting would normally be the best option, but I don't see how this can be applied as I have so many alternative choices.
Any suggestions would be greatfully received. Thank you.
ASKER
Hi Brad,
thanks for the quick reply.
Each cell in column F has validation applied that limits the user to select from a list of values (e.g. A, B, C etc). The list has 12 different choices that the user can select from, either by clicking the drop-down arrow in the cell, or by partially typing an entry from within the list.
Using code suggested on the Microsoft site, I was able to set up a macro that would format the row if the user typed in a value, but not if the user selected it from a list.
The macro will need to run automatically as soon as the user has either typed the choice into column F or selected it from the validated list that appears in each cell in that column. Upon choosing an entry, the text in the entire row will change colour to an appropriate value.
I hope that makes a little more sense. I'm quite happy to use VBA, but the 'conditional' formatting must take place as soon as the entry has been made or modified in column F.
Many thanks,
Gary
thanks for the quick reply.
Each cell in column F has validation applied that limits the user to select from a list of values (e.g. A, B, C etc). The list has 12 different choices that the user can select from, either by clicking the drop-down arrow in the cell, or by partially typing an entry from within the list.
Using code suggested on the Microsoft site, I was able to set up a macro that would format the row if the user typed in a value, but not if the user selected it from a list.
The macro will need to run automatically as soon as the user has either typed the choice into column F or selected it from the validated list that appears in each cell in that column. Upon choosing an entry, the text in the entire row will change colour to an appropriate value.
I hope that makes a little more sense. I'm quite happy to use VBA, but the 'conditional' formatting must take place as soon as the entry has been made or modified in column F.
Many thanks,
Gary
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wow!
I have never seen code used for an individual sheet before. I've only ever put a macro on a separate module.
I have just one further small request (if this isn't being too cheeky) - can you explain how to modify the code suggested so that the entire row is formatted, rather than just the cell containing the chosen list item.
I really appreciate your help with this.
Gary
I have never seen code used for an individual sheet before. I've only ever put a macro on a separate module.
I have just one further small request (if this isn't being too cheeky) - can you explain how to modify the code suggested so that the entire row is formatted, rather than just the cell containing the chosen list item.
I really appreciate your help with this.
Gary
ASKER
Increased the points to 300 as an incentive for the additional help!
Thanks,
Gary
Thanks,
Gary
Gary,
You just need to use the .EntireRow property:
Target.EntireRow.Interior.
Target.EntireRow.Font.Colo
If you want just columns A:H, then try:
Intersect([A:H],Target.Ent
Intersect([A:H],Target.Ent
Brad
You just need to use the .EntireRow property:
Target.EntireRow.Interior.
Target.EntireRow.Font.Colo
If you want just columns A:H, then try:
Intersect([A:H],Target.Ent
Intersect([A:H],Target.Ent
Brad
ASKER
Thanks for all your help, Brad. I am very impressed at the speed, detail, and suitability of your answer.
That's helped me A LOT! Best wishes,
Gary
That's helped me A LOT! Best wishes,
Gary
Gary,
Most questions don't last very long in this TA--there are a number of outstanding regulars who enjoy helping.
Just out of curiosity, how many different formats did you need? A different one for each choice, or did some possibilities double up? In this latter case, you can use constructions like:
Case "A", "B", Is > 10
Thanks for the grade!
Brad
Most questions don't last very long in this TA--there are a number of outstanding regulars who enjoy helping.
Just out of curiosity, how many different formats did you need? A different one for each choice, or did some possibilities double up? In this latter case, you can use constructions like:
Case "A", "B", Is > 10
Thanks for the grade!
Brad
ASKER
Hi Brad,
I needed 12 different formats, but there were none which needed to double up. The example previously supplied was perfectly adequate for my needs.
I can now move on to the next part of the spreadsheet solution I am trying to provide. With any luck, I won't need to post any further questions. Of course, if I do, they will be posted as separate issues and points awarded accordingly.
Thanks once again,
Gary
I needed 12 different formats, but there were none which needed to double up. The example previously supplied was perfectly adequate for my needs.
I can now move on to the next part of the spreadsheet solution I am trying to provide. With any luck, I won't need to post any further questions. Of course, if I do, they will be posted as separate issues and points awarded accordingly.
Thanks once again,
Gary
There are many ways to solve this type of problem, ranging from tools built into the worksheet interface to VBA code. Could you please tell us exactly what you need to accomplish so we can offer the most pertinent advice?
Conditional formatting has a limit of 3 different formats (plus the default format if none of the conditions apply). We can develop a formula with 12 possible choices, as long as there are no more than four format outcomes.
The cells may also have up to four formats possible, depending on cell value.
VBA code has no limit on the number of different formats & conditions. You'll need to say whether the user is changing the value in the cell, or if it is responding to a change in a formula input.
Cheers!
Brad