Formatting unknown ranges based on contents of a column

[justforthis1]

Hi all,

i can't seem to figure this one out.

I attached a file showing how i am trying to format data (the second tab).

The big issue is the counting to format. (the formatting itself is obviously easy.)

The number of columns is not always standard so i need to count for it.

the number of rows in each "room number" is also not always standard, which means i also need to count for those.

If anyone has had experience trying to do something like this i would appreciate the help.

i know it is possible because i had one that someone had given me years ago, unfortunately i lost it in a hard drive failure...also years ago.

if it isn't obvious what i am trying to do, let me know and i will try to be clearer.

[JBeaucaire]

Give this macro a try:

Please Login or Register  to view this content.

[justforthis1]

that seems to work out the basic idea, i am going to play with it a bit and see if can figure out the bugs, i will let you know what i find.

[justforthis1]

ok so i have come to the conclusion that the part that is making it not work is the "myval = worksheetfunction.max(columns(2)).

reason, some of the room numbers have letters in the number, ie 127B. i think this is throwing off the MAX function...unfortunately i can't figure out a workaround.

any ideas?

[JBeaucaire]

Yep, that would break the whole thing. MAX() needs numbers. You provided a dataset that did not properly represent all your needs. (nudge)

If you know what all the room numbers are going to be, post up a full representation and we'll take another crack at this.

[justforthis1]

Yah. Sorry about that, i didn't want to overwhelm anyone willing to help with to much information.

The best i can tell the only thing that is not accurately represented is the room numbers. At times the Room numbers could have B103, or 127A, or 127B, etc. I think that is only discrepancy.

(and the part numbers would actually be a string of letters and numbers i.e. CB78TG5, or the like, but since we are not using that column to dictate the formatting i don't think it matters.)

[JBeaucaire]

You know, if you post up a sample workbook that truly represents ALL the hurdles to overcome, perhaps I can think of something. Your original book doesn't do that, so beef it up to cover everything and let's have another look.

[justforthis1]

Ok if you think it will help.

I think this is a fairly typical example.

Thanks for your help btw.

[JBeaucaire]

Once more around the block:

Please Login or Register  to view this content.

[justforthis1]

Well i thought that last one had done it... but as i have begun playing with it it seems to freeze excel to the point of where it never recovers.

Do you have any ideas why it would be doing that?