Ok, a few questions:

1) In the workbook I've loaded at this end, the three columns you name for deletion—"Baseline Start", "Baseline Finish", and "Resource Names"—are not E,G,J as you said originally, nor E,H,J as you say this morning, but F,H,K. You say you add the first column, ID #, and if you mean you add that manually after the macro is run then that would be E, G and J again. So I'm just checking that that's what you meant...?

2) "Export"? What export? So far all I know about is coloring rows. Don't get me wrong, export is fine; but if you're doing something more than I thought, I want to know what it is in case it affects the macro.

3) If you're planning to add the ID# column every time, after the macro is run, why not have the macro do it for you? One less thing to do manually. But we can add that later; it doesn't have to be now.

I notice one other thing about this worksheet, which isn't exactly a question but I'll add it to the others:

4) We've been talking about doing this to rows 1 through 264; but row 1 contains column headers. The program as we've discussed it wouldn't change change the color of row 1 (because it has neither "Phase" nor "Sub-Phase" in the "View Filter" column), but we may as well change it to run from 2 to 264 instead.

Other than the above, I think the program looks about right. Now: Do you know how to get Excel to run that program? Should we walk through it step by step, or do you already know what to do next?

Note: I am going to scream. I've typed a response twice now only to accidentally click the back button on my browser and have it erased- so I apologize if any of my response doesn't make sense, as I'm sick of typing it over and over again

1 & 2) I perform an export from MS Project to Excel for my client to review each week. When the export is performed, only columns B-O are present. Therefore, the columns I wish to delete (Baseline Start, Baseline Finish, Resource Names) all line up in columns E, G and J.
3.) Yes, the ID# column is something I'd like for the macro to perform for me. The purpose of this column is so that if there is a change made on the Excel document, I can easily locate it in MS Project. The ID# pertains to the row# in Project.
4.) Yes, I apologize- I was looking at the ID#'s and confused it with the row. We should be looking at rows 2 and beyond.
*Note: As there are changes made to the timeline itself (tasks added/subtracted) we may not always be dealign with rows 2-264. It could be 2-270, 2-275 etc. as things are altered. Is there a way to just have the macro review the workbook instead of a specific range of rows?

Lastly- I took your last post involving code and implemented it to see if it would work (I changed "For jr = 1 To 264" to be 2 To 264). However, it errors and gets hung up on the following (in red):
Case "Phase"
ce.ThemeColor = xlThemeColorLight2
ce.TintAndShade = 0.799981688894314

The Error says: "Run-time error '438': Object doesn't support the property or method". When I click Debug, the line I highlighted in red font is highlighted in yellow

Don't you just utterly hate that? I'm a writer, myself, and when either I or my software sends my blood, sweat and tears to the planet Zembar (as Dave Barry once claimed after they installed word processors at the Miami Herald) I'm so heartsick I can barely work up the enthusiasm to start over. And I'm sorry to tell you that although I love programming, programming is writing; the same discouragement applies there, when you accidentally delete something you just spent all morning writing. (So learn to keep copies...though even if you do, you're bound to not, once in a while.)

Yes, we can easily (relatively easily) get your program to locate the last row in the worksheet and process all the rows from 2 to that, instead of to something hard-coded like 264. Let's do that next, and add the extra column third. First we'll fix the error.

The error is my fault. TintAndShade and ThemeColor are properties not of the Cell object directly but of some sub-property; I think Cell.Interior is involved, but there may be more. The original program had it right, and I over-simplified. Try changing it to ce.Interior.ThemeColor and ce.Interior.TintAndShade (adding the ".Interior" part to both) and see whether that fixes it. If not, take a look at the original code Excel recorded for you, and maybe what you need to change will be obvious from that. (Sure, I could look for myself; but what would you learn from that?)

And I should introduce you to some other features of the VBA Editor, too, so that when I or someone else makes a similar bone-headed goof like that, you won't be helpless until you can get someone to answer you: There are ways you can figure it out for yourself. (I'm a teach-a-man-to-fish sort, you see.)

OK. So, that works (sort of). Of course, you could have just intended that as a starting point.

It only colors the cells in column L that contain 'Phase' and/or 'Sub-Phase' - whereas, it should color, for example, A#:L#, based on the criteria contained in column L

I'm sure I am jumping the gun on that, but wanted to make sure I mentioned it now before we move on!

Doh! Sorry, I didn't think that through. So you want to look at the value of L & jr, but based on its value you want change the color (the ThemeColor and TintAndShade) of Range("A" & jr":L" & jr). So try this:As before, you don't have to name it ColorRange; I just picked that because I didn't want to take the time to make up a shorter name that still describes itself. I set ColorRange to the part of the row we want to color, I include Interior as part of it (so I don't have to repeat it on all four lines below) and then go with that.

This should work—but does it make sense to you? If you understand what I'm aiming for, you stand a better chance of spotting problem and of improving on it yourself later.

This is how I have it written, and it keeps saying "Compile error: Sub or Function not defined". It highlights 'Sub Main()' and doesn't even start to perform any of the tasks. For the most part, ignore what I have identified for columns to delete and the rows for jr. Mainly because I am writing this against sample data before i incorporate into the real thing...

Note* I tried to write the 'Set ColorRange = Range' portion exactly as you had it written- even copied and pasted at one point- but it tells me "Compile error: Expected: list separator or )" and highlights the: ":L" portion of what's written. I played around with it, and what i have written below is the only thing it would accept.

Hi,

My 2 cents.
You have
Should be

As in Bobs code.

Charles is right, EC: I'm used to writing in another language that allows concatenations merely by sticking variables and constants next to each other (egIn VBA that must be...but I left out one of the ampersands. I'd like to tell myself this is good, that you'll benefit from seeing another's errors and working out the solution, but the fact is there was nothing to keep me from testing it for simple syntax errors before I showed it to you, just in case...because simple syntax errors are so very common.

Oh, wait, I have a better excuse than I remembered: Two of my sons showed up unannounced to take me out to a sports bar and watch the first US game (against Ghana) in the World Cup for my 60th birthday. So I was sort of in a hurry to finish my response and send it to you. The US won, too!

Still, I apologize. To make it up to you, I have tested the remaining program, even though it's 'way past my bedtime (sigh, moan, and a surreptitious glance to see whether I'm making an impression) and watched it work for real. I ran it straight off the sample you sent, and therefore some parts had to be changed to match the current columns, so it looks like this:(The apostrophe in the second line marks the rest of the line as a comment, which the VBA language will ignore until I take it out again.) Now, my excuses and failures aside, you really should be learning something from each mistake I make. In this case, the '&' operator in VBA is used to concatenate character strings; I momentarily thought I was writing in REXX instead of in VBA and left one out, which is why Excel balked at running it, but Charles had no difficulty figuring it out. He knows VBA and you don't, so I don't blame you for asking for help—help that you shouldn't have needed from me had I done my job right—but I do want you to understand what happened for next time. Questions?

And if it works this time, the next task is to teach the program to find the last row, so you don't have to make it run from 2 to 264 but can make it smarter.

holy shnikeys it worked...
I certainly think that I am learning something here And i figured it would be best for me to run it in a test environment first, so I can then translate it to the 'real thing' after- that will allow me to re-write things, make mistakes and hopefully debug for myself. Maybe bug you a couple more times in the process..

onto the next item! I'm going to play around with the code for the time being (simple thing- I will color my header row..) while you prepare the next item

I think we can skip the lesson on teaching the program to find the last row. I found out that Project has a column already titled "ID". In which case, that would take care of that item automatically.

So the next lesson issss... grouping.

I want the program to automatically group the sections. The Spreadsheet I provided previously as an example for you, I've reattached with the items grouped exactly how I would like them to appear.

Each Sub-Phase should be grouped & then, each Phase should be grouped. Again, keeping in mind that the number of rows each each Sub-Phase may change as tasks are added and/or subtracted from the project. Therefore, the grouping needs to be specified in some fashion. Hopefully you can identify how we can be specific here...

Attached Files

Example Export.xlsm (22.6 KB, 6 views)

Ok, the next item. But first, this word from the Teacher: It's probably obvious to you by now, but you have to understand that writing macros isn't magic; each statement in the program does some particular thing (kind of like a series of steps in an algebraic solution), and by following each instruction—each of your instructions—Excel accomplishes what you wanted...or doesn't, if your instructions were faulty. So if I tell you something and it isn't clear to you why or how, then ask.

Ok, you have a program that deletes some columns, then runs from rows 2 to 264 and colors certain rows. Now let's show it how to spot the list row and do the coloring only up to that point.

There's more than one way to do this. (Programmers always say that, and it's always true.) We can have the program simulate a <Ctrl-Ins>, which jumps to the last used cell in the worksheet. My own favorite is to do <End>,<Down>, which works great if you're in a column that has data all the way to the end, with no empty cells I mean. If there are empty cells in the column you want to use, you can go to the bottom of the sheet, or at least so far down that you're confident that it's past the end of the worksheet (say row 99999), and then do <End>,<Up>, which will jump to the first row —the first row, that is, that it encounters in the upward direction — that isn't empty. Many programmers would find it natural to just tell the program to keep going from row 2 onward, and stop when it gets to a row that's empty. And I'm sure there are other ways; there always are.

Feel free to decide for yourself what makes sense to you, because programming styles are as varied as programmers; but I'll start with the one that I like. For this we're going to start with row 1 and any column that has data unbroken from top to bottom. I'll use column K ("View Filter") in the worksheet I'm looking at. The sequence, then, will be this:

1) Start with K1.
2) Hit <End>,<Down>. Excel finds the next empty cell in the "Down" direction, and puts the cursor just above it.
3) What row is that?

In VBA it looks like this:The first statement finds K1 and points to it using the variable name "c1", which I made up arbitrarily.

The second statement says to start with c1 and from there do an <End>,<Down>. Only instead of hitting two keystrokes, in VBA End is a sort of sub-program (called a "method") that works on cells and xlDown is a keyword to VBA/Excel that tells it the direction. What End hands back as a result is another cell, the one at the bottom of data. That cell, K23 in the worksheet I have here, is now labeled c2 in my program.

The third statement asks for the Row "property" of c2 (which of course is 23), and puts that in a variable I named "rz", which in my programming style means the last row. (You will inevitably work out names for things that suit you, not me; that's as it should be.)

So the result of those three statements is that rz is set to 23, and then your next statement is...instead of "...to 264", you see.

Only we're not going to do three extra statements. You can combine them in various ways:I'd probably go with 5 or 7, myself, but it doesn't matter; they all do the same. What matters is that it makes sense to you, so that if you look at it six months from now (by which time you will have forgotten the details) it'll be clear to you what you were doing and why. This, by the way, is why programmers say you should write as many comments as you think you need to help you remember:Let me know how that goes (whichever form you use) and we'll talk about the next step.

Sorry, I must have started that find-the-last-row post before you said you didn't need it.

Grouping: It happens I had to do that at a recent contract. It's obviously useful to automate, because when it's needed at all it's often needed with lot of data. Let's see, here... Ok, I found it. You use the Group method on whole rows, apparently. What you have to do is get your program to identify a clump of detail rows and Group those, and then identify a bunch of sub-phases (and their details) and Group those, and so on rolling up to the top. I had a number of levels in mine, up to five I think; you have only three, if you count the whole as one.

I simply must get some work done, so I'm going to leave you for now with just this: With the outline in your data cleared (or with another worksheet that isn't outlined) get your program to do something like this:What I think that will do is set up a bottom-level grouping for rows 5 through 9.

If that works, you can see that all you need to do is explain to your program how to find the proper rows for each grouping and do the same throughout your data. I'll come back tomorrow (maybe tomorrow) and we can work on how to do that. That part is a little more complicated than what you've done so far, but only a little.

Where you asked that I perform the End Down function while in K1, and asked what row I was in- it shot me to the last possible row (1,048,576). Not sure if that was what it was supposed to do or not! But my answer is 1,048,576!

My only question for this part of the lesson is this: If we are coloring the rows simply on the content of the cells contained in "View Filter" (i.e. Phase, or Sub-Phase), is it really necessary to tell the program where it should stop when coloring the rows?
I would imagine once it no longer sees Phase or Sub-Phase, it is no longer trying to color anything.

However! I have had some very minimal experience with other people's macros (before I decided I should start to try this myself. and my previous experience was at a previous employer- so there's no asking for help other than on here ). I do recall a colleague had a macro that did something similar- but was not coloring rows, but just individual cells. Sometimes, when I or someone else would run it- it would freeze up. And when it would finally come to, the macro colored a ton of empty cells that were completely unnecessary.

Perhaps that is why you have explained the above?
Either way, I have included it- tested it, and it didn't cause any issues- so we're good to proceed

For the most part, I understand- as much as I can understand with no prior knowledge of the language of a programmer, or knowledge of macros in general. I am big on trial and error, but I need a stepping stone to even allow me to perform a trial. Then I can troubleshoot after I have a basic understanding.

So I am picking up what you're laying down, but at the same time, I might not be able to pick up all of it. I'm picking up what I can, and applying it, and it's a good starting point. I am making those comments in there so I can refer back some day- so that is helpful. From there, if I ever get stuck, I can resort to the wonderful world of Google- which is what brought me here to begin with!

ha! and I typed that response before I saw yours...

Got it. The simple grouping code I picked up from when I did my initial "record macro". So that is easy enough- but what I'm hoping to do is give it a criteria to use when grouping, rather than providing specific rows.
I produce my export every Thursday, so I can manually group until we can program it to do so !

Oh yes. Work. I should probably get back to doing that as well...