Solved: Conditional Formatting

[Ismael]

Hi to all in the forum,



I have a doubt regarding the conditional formatting.



I look this post



http://www.vbaexpress.com/kb/getarticle.php?kb_id=90



But I don?t understand the code, so I couldn?t change it to my need.



So as you guys can see in the attach file, in column?s B, C, and D I have values, what I pretend is represented in column J:L, this is the best solution for my problem, but if this isn?t possible the solution that I represent in column?s F:G will work too.



So if any of you guys can help me I really appreciate.



Note:



In the attach file I have values in 3 column, but this values can be in 1, 2, 3, 4 or in the maximum in 5 column. (This different ?states? are represented in the file attach).



Best regards,



Ismael

[MWE]

Hi to all in the forum,
I have a doubt regarding the conditional formatting.
I look this post

http://www.vbaexpress.com/kb/getarticle.php?kb_id=90

But I don?t understand the code, so I couldn?t change it to my need.

So as you guys can see in the attach file, in column?s B, C, and D I have values, what I pretend is represented in column J:L, this is the best solution for my problem, but if this isn?t possible the solution that I represent in column?s F:G will work too.

So if any of you guys can help me I really appreciate.

Note:
In the attach file I have values in 3 column, but this values can be in 1, 2, 3, 4 or in the maximum in 5 column. (This different ?states? are represented in the file attach).

Best regards,

Ismael

it appears that you really want to do 2 things:
1. group all cells with similar text
2. format that merged group with borders, colors and font

You are really stretching "conditional formating" because normally conditional formatting operates on a single cell at a time. Further, it may end up doing something you did not want. In particular, Conditional Formatting (or the equivalent KB stuff), works every time you change anything on the sheet. Thus you will not really be able to do several manual things and then have "conditional formatting" execute. Rather it will execute every time you press enter or do some Excel operation. Also, there will be no way to "undo the formatting", except manually.

I could rough out code code to do what you want or massage the KB code for the same end. As an alternative, consider a macro the either "formats" the way you want or unformats the colored groups back to their original state. The macro could "toggle" back and forth each time you call it, formatting into colored groups the first time, unformatting back to "original" the next, etc. You would control when the macro executes via a button or similar. Would something like that work?

An alternative to toggleing would be something where you explicitly state that you want the macro to group & color or "undo". It would also be easy to make the macro work on just some area of the worksheet or the whole worksheet.

[Ismael]

Hi MWE,
In first place thanks for the replay.
You confirm my fear, that is the conditional formatting don't work whit merge cells.
Ok, but it's possible to group all cells with similar text, this works for me too.
The area that I want to format in my original sheet is B3:BH52.

Note that the content of cells isn't always the same, sow once more you have reason, because I need that the conditional formatting change every time that something happens.

I don't full understand what you said about the macro, but for me doesn't exist any problem on do that whit a macro.

So if you can help me whit this, I really appreciate.

Best regards,

Ismael

[MWE]

Hi MWE,
In first place thanks for the replay.
You confirm my fear, that is the conditional formatting don't work whit merge cells.
Ok, but it's possible to group all cells with similar text, this works for me too.
The area that I want to format in my original sheet is B3:BH52.

Note that the content of cells isn't always the same, sow once more you have reason, because I need that the conditional formatting change every time that something happens.

I don't full understand what you said about the macro, but for me doesn't exist any problem on do that whit a macro.

So if you can help me whit this, I really appreciate.

Best regards,

Ismael

Ismael: a few clarifications:
1. conditional formatting will work with merged cells, but it will not do the merging
2. is the B3:BH52 range the only range where the magic is to occur?
3. my explanation about the macro was just to give you a feel for what could be done. I will put something together for you to try and you can then decide if it is about what you want.

[Ismael]

yes, the magic can occur just in the range B3:BH52.

Ok, I will wait, for you tips, and then try to do something.

Best regards,
Ismael

[MWE]

yes, the magic can occur just in the range B3:BH52.

Ok, I will wait, for you tips, and then try to do something.

Best regards,
Ismael

Ismael: I have cobbled together something that does what you want. I made it a bit more general than might be required for your case. At present it only works on the single sheet, but extending it to work on any sheet is pretty easy. It also only creates groups or clusters. Let me know if you want to "ungroup" as well.

[Ismael]

Hi MWE,

Thank you very much for your precious help.

What you done it's unbelievable, the macro does exactly what I pretend.

Regarding the possibility of "ungroup" the cells, if this doesn't give you much work, yes I would like to have an option to do this.

MWE, once more thanks for the help.

Best regards,

Ismael

[MWE]

Hi MWE,

Thank you very much for your precious help.

What you done it's unbelievable, the macro does exactly what I pretend.

Regarding the possibility of "ungroup" the cells, if this doesn't give you much work, yes I would like to have an option to do this.

MWE, once more thanks for the help.

Best regards,

Ismael

Ismael: glad to help. This was fun to do and has spawned some ideas and code that will be useful in the future. I will work on the "ungroup" capability sometime today.

Regards,

MWE

[MWE]

Ismael: attached is an updated spreadsheet with "ungroup" capability.

MWE

[Ismael]

Hi MWE,

Once more thank you very much for all your help.
But if isn't abuse of you I have one more question.

That is, as you can see in the file attach, I have fill several cells whit the values that exist in my original file.
The content of this cells are allways this kind:

12:00 TP 103 15:00, the only thing that change is the letters that can have 2 or 3 characters.

What I pretend is represented in the file attach (Sheet Color example), in simple words, What I pretend is fill whit different colors the merge that "magic" does, instead of all cells stay whit the same color (this is what MakePretty does).

In the example you can see that I fill whit the same color the cells that letter "TP", and I made the same for the others.

So is this possible??? If is I ask you to make this for the names that I put in the file attach, and then I will make for the others that I have.

So if can help me once more, I really appreciate.

Thanks in advance.

Best regards,

Ismael

[MWE]

Hi MWE,

Once more thank you very much for all your help.
But if isn't abuse of you I have one more question.

That is, as you can see in the file attach, I have fill several cells whit the values that exist in my original file.
The content of this cells are allways this kind:

12:00 TP 103 15:00, the only thing that change is the letters that can have 2 or 3 characters.

What I pretend is represented in the file attach (Sheet Color example), in simple words, What I pretend is fill whit different colors the merge that "magic" does, instead of all cells stay whit the same color (this is what MakePretty does).

In the example you can see that I fill whit the same color the cells that letter "TP", and I made the same for the others.

So is this possible??? If is I ask you to make this for the names that I put in the file attach, and then I will make for the others that I have.

So if can help me once more, I really appreciate.

Thanks in advance.

Best regards,

Ismael

Ismael: if I understand what you would like changed, you would like each group that has a similar "code", for example "TP" or "AZ" or "LH" to have a particular color. Easy to do. However since there are many colors that we could choose, let's define a code to color relationship so you get to pick what color, say, "TP" is.

I will add a sheet called "ColorCodes" and will put in the codes already supplied on your tab named "ColorExample". You can then add codes, change colors, etc. The procedure will be smart enough to figure out how many codes and colors there are. I will also include a special code called "NoCode" and that color will be selected if the procedure encounters a code that was not found in the ColorCodes sheet. Do you also want the opposite? That is, if a particular code found on the ColorCodes sheet is not found when the procedure searches the target worksheet, a message or something will be displayed.

Finally, I recommend that if a darker color is used, we change the font color to white. If you look at your sample and change the font color to white for those codes that are quite dark (maroon, dark green, dark blue, etc), you will notice that it is much easier to see things. Further, If you want to print this out on a black and white printer, the black letters on a dark background are compeltely lost. An alternative to just white or black is a tweak to the ColorCodes sheet. For each code, there will be a cell color and a font color. You get to pick what combinations you want.

I will make the mods today and post back in a while.

Finally, to help you communicate with the forum a little better, a few hints about the words you use.

• you use "whit" when I think you mean "with"
• you use "pretend" when I think you mean "want" or "desire"

In general, your English is pretty good (and certainly a lot better than my Portugese -- heck I can not even spell it!) .

[MWE]

Ismael:
the attached does everything as per my last reply:

• groups are color coded as per sheet ColorCodes
• if a group code is encountered that is not found in the ColorCodes sheet, it is set to the NoCode color
• the ungroup procedure has been tweaked to make sure fill color is "none" and font is "black"
• I have not added the message warning that a code found in ColorCodes was not found on the target sheet.

try it and let me know if it does what you want.

[Ismael]

MWE, your are amazing, the file does exactly what I desire.

The Idea of change the font color to white if we use a darker color was very good.

MWE, thanks for your tips regarding the words that is better to use, I appreciate that, because my English isn't perfect, but I will continue to practice.

So MWE, I don't now how to thank you the excellent job that you have done.

Thanks men.

Best regards,

Ismael