NEWB to Macros

EC37 · #1 06-11-2014, 07:37 AM

Hi

I've never used Marcos until today. I have been trying to expand my knowledge with Excel as much as possible, and I wanted to automate an export I do on a weekly basis. I used the "Record macro" function to record everything I do during my formatting process (it's fantastic!).

My question is this: I am producing an export of a timeline from MS Project. Then formatting Phases with one color, sub-phases with another color, removing columns etc. As we move along with the Project itself, we have been identifying tasks that were not previously included on the timeline, and they should be. So, I will insert tasks as needed- therefore, changing my macro because due to that new line, it in turn changes the row # that it should be color formatting. I'm fine with re-recording the macro as things change, because eventually, the need to do so will become less or non-existent toward the end.

I know I can go in and edit the code accordingly each time as well, but what I was wondering is: Can I alter the code to be more specific in regards to what it will format, rather than specifying by row# or column#? For example:

Row 245: (cells read as follows) A245: 2,046, B245: 0%, C245: Data Processing, D245: 3 (the remaining data in the table ends in column H)

Can I identify in the code, that I want it to find the cell containing "Data Processing", and color that row from cell "A###:H###" ?

Additionally, part of the formatting requirements is to group the cells by Phases and Sub-Phases. Similarly, can I identify in code to find "Data Processing", and group from that row to the row containing "Process final data" (the final task in that Phase)? that is what I end up finding to be more difficult when editing what is already there- back tracking and changing which rows to group from/to.

Final question! I realized as well, when I assigned conditional formatting requirements, that it did not record that piece of the puzzle. Unless there is another way to log the conditional formatting requirements? I'm also fine with just assigning the conditional formatting after I run my macros- but if it can be automated as well- all the better

Any help is greatly appreciated!

Thanks!!
E

BobBridges · #2 06-11-2014, 12:57 PM

Yes! You can do almost anything in a macro—and definitely that.

(Off-hand I can't think of anything I can't get a macro to do that can be done at all, barring such things as "raise my children" and "pray". But as soon as I scratch "almost", someone will think of an exception. So I'll stick with the cautious construction.)

An MS-Office macro is written in the VBA programming language. All programming languages are sort of like algebra; they're full of very exact definitions for every symbol, and if you've never done one they can look intimidating, but once you get the hang of it you can use those symbols to give explicit instructions to Excel and feel that you really do know what you're doing.

If you're interested in learning, I suggest you post the code that you recorded, and I (or someone) will give you some idea of what each statement does. Or at least the first few statements, if it's long. Then I'll suggest a change or two, a change that will be simple for you to execute but that will have some immediate and obvious result. That, for me when I'm learning a new programming language, is a big step forward in feeling that I'm more or less in control; there's more to learn but at least I see that I can do this, you see.

From there we can proceed until you have it working the way you want, and understand it so you'll be able to do still more the next time.

EC37 · #3 06-11-2014, 01:03 PM

Great! I am actually sign off for the day, but will see if I can provide you with something later tonight or by morning. I appreciate the help!

EC37 · #4 06-12-2014, 05:56 AM

OK, good morning! Here we go. I will paste to you certain sections where I am deleting columns, formatting selected cells in a row, and grouping items. All of which, I would like to specify based on a particular cells content.

The following is the start of things, deleting columns that are unnecessary:

Code:

Range("E:E,G:G").Select
Range("G1").Activate
Selection.Delete Shift:=xlToLeft
Columns("H:H").Select
Selection.Delete Shift:=xlToLeft

The actual column names are:
Column E= "Baseline Start"
Column G= "Baseline Finish"
Column H= "Resource Names"

The following code is formatting selected cells in a row:

Code:

ActiveSheet.Range("$A$1:$L$264").AutoFilter Field:=12, Criteria1:="Header" 
ActiveWindow.ScrollColumn = 2 
ActiveWindow.ScrollColumn = 1 
Range("A2:L11").Select 
ActiveWindow.Zoom = 60 
Range("A2:L11,A43:L43,A103:L103,A134:L145,A186:L254").Select 
Range("A186").Activate 
With Selection.Interior 
    .PatternColorIndex = xlAutomatic 
    .ThemeColor = xlThemeColorLight2 
    .TintAndShade = 0.799981688894314 
    .PatternTintAndShade = 0 
End With 
Range("A12:L25,A44:L94,A104:L118,A146:L181,A255:L261").Select 
Range("A255").Activate 
With Selection.Interior 
    .PatternColorIndex = xlAutomatic 
    .ThemeColor = xlThemeColorDark1 
    .TintAndShade = -4.99893185216834E-02 
    .PatternTintAndShade = 0 
End With

Example: For A2:L11. In row 2, I would like to format this row from cells A2:L2 based on the information contained in cell C2 "Scope / Design". In row 11, format cells A11:L11 based on the content contained in C11 "Development". In row 43, format cells based on content in C43 "Logistics".. and so on.

The following code is to group different sections:

Code:

Rows("147:164").Select
Selection.Rows.Group
Range("A151").Select
ActiveWindow.SmallScroll Down:=15
Rows("166:171").Select
Selection.Rows.Group
Rows("173:180").Select
Selection.Rows.Group
Rows("182:185").Select
Range("A185").Activate
Selection.Rows.Group
Rows("146:185").Select
Range("A185").Activate
Selection.Rows.Group

And here, first I group the sub-phases, and return to group the entire phase.

So, for instance I'll give you the info on the first sub-phase and last sub-phase of this phase: 147:164 are all the tasks contained in the sub-phase in cell C146 ("Small - Counts"). 182:185 are tasks contained in the sub-phase in cell C181 ("Pull List"). And the Phase itself, contained in cell C145 ("Sample").

all of the specific information I want to use when formatting by colors and grouping, is located in column C, which is the column for "Task Name"

Let me know if you need more information.

BobBridges · #5 06-12-2014, 07:11 AM

I'll start looking at this, posting comments as I go. On that first section, I see you're deleting columns E, G and J. Here's how I figure that:

Code:

  Range("E:E,G:G").Select           'selects columns E and G
  Range("G1").Activate              'doesn't do anything, from your point of view
  Selection.Delete Shift:=xlToLeft  'deletes the two selected columns
  Columns("H:H").Select             'selects the new column H (was J)
  Selection.Delete Shift:=xlToLeft  'deletes the selected column

The code says to delete column H, but before you deleted E and G that column was J, right? You can accomplish all that by just one statement:

Code:

Range("E:E,G:G,J:J").Delete Shift:=xlToLeft

This is no way a complaint about your skills; you said at the start that you'd never done this before. It's just that the VBA macro recorder writes down every single action—and when you're doing it manually, you necessarily use (and it records) keystrokes that aren't necessary in the program. For example, there's no need, in the program, to select the cells and then delete them.

Now let's take a look at the next section....

BobBridges · #6 06-12-2014, 07:23 AM

Ok, now I'm looking at that second section. There are a number of recorded actions here that I take it are not necessary to the work: you scroll left twice, and you zoom out a bit, both so you can see the data you want to work with. But when we transfer this to VBA, the program doesn't need to look at the data—or rather it doesn't need to have the cells in the window in order to look at them. The part that matters for your program is this:

Code:

  Range("A2:L11,A43:L43,A103:L103,A134:L145,A186:L25 4").Select
  With Selection.Interior
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorLight2
    .TintAndShade = 0.799981688894314
    .PatternTintAndShade = 0
    End With
  Range("A12:L25,A44:L94,A104:L118,A146:L181,A255:L2 61").Select
  With Selection.Interior
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -4.99893185216834E-02
    .PatternTintAndShade = 0
    End With

The part that sets the formatting would stay the same, presumably; what you need in your code (right?) is some way of identifying automatically the ranges you want to work with, instead of you having to inspect them and set them manually. So here's the first working question: Why did you select those ranges? Will your program be looking for the word "Totals:" in A25, A94, A118 and so on, or what? What's the definition?

EC37 · #7 06-12-2014, 07:41 AM

Those ranges are selected because, in MS Project, they are the Phases and/or Sub-Phases of the project. Those titles rarely change- I would say it is a slim to none chance that they do. In the very beignning as we were determining the timeline, yes, certain wording was altered- but the chance of me having to change something between now and the end of the Project, is very very slim.

So, that is what would determine how and when I will format the background color of a selected range of cells (based on the information provided in column C). So, as I provided in my example- cell C2 contains "Scope / Design". Therefore, cells A2:L2 should be formatted with the appropriate color I had chosen.

BobBridges · #8 06-12-2014, 11:37 AM

So the ranges, including the rows—A2:L11, A43:L43, A103:L103, A134:L145, A186:L25, A12:L25, A44:L94, A104:L118, A146:L181, A255:L261—are all probably static? That's unusual, but it does make things easier.

In your example you used only two different colors, and I take it that means the value in column C had only two values. How many will there be in all? I mean, will all your ranges be either one color or the other? Or will there be a number of possibilities?

Just now I envision your program looking something like this:

Code:

Sub Main()
  Range("E:E,G:G,J:J").Delete Shift:=xlToLeft
  Color Range("A2:L11")
  Color Range("A12:L25")
  Color Range("A43:L43")
  Color Range("A44:L94")
  Color Range("A103:L103")
  Color Range("A104:L118")
  Color Range("A134:L145")
  Color Range("A146:L181")
  Color Range("A186:L254")
  Color Range("A255:L261")
  End Sub

 ' Color the supplied range.
Sub Color(Rng)
  Select Case Rng("C1").Value
    Case "X": With Rng.Interior
      .ThemeColor = xlThemeColorLight2
      .TintAndShade = 0.799981688894314
      End With
    Case "Y": With Rng.Interior
      .ThemeColor = xlThemeColorDark1
      .TintAndShade = -4.99893185216834E-02
      End With
    Case Else:
    End Select
  End Sub

The parts in green are, roughly, what I copied from your old program; the parts in red are what I added and what I presume you'll need me to explain. What it's would do is this:

1) Delete those three columns, as before.
2) One by one, call a new subroutine that I named "Color", supplying a different Range with each call.

That's in the Main routine. Color, each time it's called, receives a range from the Main routine and does this:

1) Looks at column C in the first row of the supplied range.
2) Based on what it sees in that cell, it applies the ThemeColor and TintAndShade properties to the whole range.

We'll have to fill in a few facts yet:
a) All the values that might be in C1.
b) All the corresponding colors.
c) What you want it to do if the contents of C1 don't match any of your expectations.

And I haven't tested the program; it may need some tweaking. Also I expect you to ask questions so that you understand what it's doing and why. That way you stand a better chance of fixing it later, or enhancing it when you see something more you want it to do. Your questions might be about the Select statement and how it works, and about subroutines, and other things.

EC37 · #9 06-13-2014, 07:35 AM

OK, so, lots to take in

and I appreciate your help..

I feel stupid by asking what you mean by 'static' when referring to the ranges. The ranges do look a bit odd, but that is because first, I filtered the rows to only show me the rows identified as "Header". However, we can't simply color based on that information- because Header not only identifies the Phases, but sub-phases as well. It is something I can certainly change, now that i Think of it- to make the code a bit easier to write. We could simply color cells based on the words "Phase" and "Sub-Phase" in column L. Phase is one color, sub-phase is another.

Without getting too far off topic from your response- I will try to de-code in my own dumbed down version of what I'm looking at

.
First delete the identified columns.
Second, you're identifiying the ranges that need to be colored in it's own subroutine(?).
Third, you're then telling the program what to do with that subroutine once selected, and to color it based on the value of X (Select Case Rng("C1").Value
Case "X": ). Although, I don't quite understand the "C1" here. Is that identifying the column? or specifically cell C1??
Fourth, in the event value of Y is found, it would color it accordingly.
And lastly, end code

If that is at all incorrect, then you can ignore the rest of this (or continue reading because I'm going to incorporate my initial suggestion from above!).

Instead of selecting several ranges, what if I were to just identify 1 range (the table). Say, cells A1:L264 (I forget the exact range, I don't have the sheet open because it's at work and i don't have VPN access

). So it would read as-

Sub Main()
Range("E:E,G:G,J:J").Delete Shift:=xlToLeft
Color Range ("A1:L264")
End Sub

' Color the supplied range.
Sub Color(Rng)

Then, tell it to look in column L, and color based on Value Case "Phase". Then, tell it to color the second piece based on Value Case "Sub-Phase".

So:

Select Case Rng("L1").Value
Case "Phase": With Rng.Interior
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.799981688894314
End With
Case "Sub-Phase": With Rng.Interior
.ThemeColor = xlThemeColorDark1
.TintAndShade = -4.99893185216834E-02
End With
Case Else:
End Select
End Sub

Am I at all correct in interpreting the code and applying it in the above?

BobBridges · #10 06-13-2014, 08:15 PM

"Static" can be a technical term in VBA, but I was using it informally; I meant that the size and location of your ranges will be the same for weeks or months at a time. When I write an Excel program, usually before it can get down to work it has to identify the rows I want to process, for example by finding out which rows have "Total:" in column A, or by looking in a column for a change from one area code changes from one to the next. In your case, if I understand you, you don't have to do that—you can count on processing the same set of rows—they remain "static", in other words, without changing. Oh, they might change at some point, but seldom enough that if they do you can just go into your program and change the code. The opposite of "static" in this sense would be "dynamic", which would mean the they change a lot.

Now about your idea for working with just one range instead of a bunch: What I thought was happening (but I knew I might be wrong) is that you had some specific ranges that you wanted to color according to column C in the first row of each range. (That's why I talked in the subroutine about "C1".) But from your new proposal, it sounds like what you may want to do is color each row—each row in the whole range—based on a value in that one row. Is that right?

Like this: look at L1 and color that whole row based on whether L1 contains "Phase" or "Sub-Phase". Then color row 2 based on the contents of L2, and row 3 based on L3, and so on down to the end.

Is that right? If so it makes the job simpler—but if I'm wrong, we can still do it. I just have to be sure I understand what you need to accomplish.

That's very often the longest part of the job, by the way.

Also by the way, another time if you have to ask a question like this, it may save some time, yours as well as mine, if you post an actual workbook with either real or sample data, and a demonstration of the result that you want.

EC37 · #11 06-14-2014, 05:23 AM

Yes. I was rethinking that actually. I didnt want to post a live version of the export since the data is confidential. But perhaps, if I have time Monday morning, I can produce something similar with dummy data involved. To be continued...

BobBridges · #12 06-14-2014, 09:14 AM

I understand about confidential data. And in this case perhaps we can figure it out using just words. But a five-row sample with dummy data can sometimes make a situation a lot clearer.

Don't forget my question below: Do you want your program to color each row based on the value in col L of that row? Or do you want it to color (for example) A2:L11 based on the value in L2? I thought at first you meant the latter; do you mean the former?

EC37 · #13 06-14-2014, 06:58 PM

Sorry. I want it to color ex- A2:L2, based on information in column L.

But I will work on that dummy data to be sure we're on the same page with it all. Make sure we're both on the same page

BobBridges · #14 06-14-2014, 07:57 PM

In that case, here's what I think your program might look like:

Code:

Sub Main()
  Range("E:E,G:G,J:J").Delete Shift:=xlToLeft
  For jr = 1 To 264
    Set ce = Range("L" & jr)
    Select Case ce.Value
      Case "Phase"
        ce.ThemeColor = xlThemeColorLight2
        ce.TintAndShade = 0.799981688894314
      Case "Sub-Phase"
        ce.ThemeColor = xlThemeColorDark1
        ce.TintAndShade = -4.99893185216834E-02
      Case Else
      End Select
    Next jr
  End Sub

Here's what it does:

Code:

Sub Main()
  .
  .
  .
  End Sub

Those are the statements that mark the beginning and end. You don't have to call it "Main"; you can call it pretty much anything you want, and in fact you should because whatever you pick is the name that will appear in your macros list whenever you want to run it. The name doesn't affect how it runs, it just makes it easier for your eyes to light on it.

Code:

Range("E:E,G:G,J:J").Delete Shift:=xlToLeft

You already get that part, I think. Next:

Code:

  For jr = 1 To 264
    Set ce = Range("L" & jr)
    .
    .
    .
    Next jr

This is one of the cool parts of VBA programming—well, programming in any language, really. The first and last statements here execute whatever's between them 264 times, each time with a different number: "JR" is a value that with each iteration changes from 1, to 2, to 3, to 4 and so on up to 264 before it quits. It will not have escaped you that these are the row numbers you said you want to handle.

The first statement I wrote inside the "loop", the underlined statement, works like this:

1) Start with the letter 'L'
2) Stick the current value of JL on the end of it—so throughout the loop it's "L1", "L2", "L3" and so on up to "L264".
3) Take that concatenation of "L" and the number and use the result in the Range method; so throughout the loop we're talking about Range("L1"), Range("L2") and so on.
4) Set "ce" to point to that range, so that for the rest of that iteration of the loop, "ce" means "Range(L-whatever)".

You don't have to use "ce" as the name of this pointer; you can pick pretty much anything. I like short names, and "ce" means "Cell" to me. So inside the loop, "ce" means cell L1, L2, L3 and so on. And by the way you don't have to use "jr" as the name of the loop index, either. Out of habit I use 'j' and one letter for my loop counters, and the 'r' in "jr" just means "row". But you can name it anything you like.

Code:

    Select Case ce.Value
      Case "Phase"
        ce.ThemeColor = xlThemeColorLight2
        ce.TintAndShade = 0.799981688894314
      Case "Sub-Phase"
        ce.ThemeColor = xlThemeColorDark1
        ce.TintAndShade = -4.99893185216834E-02
      Case Else
      End Select

The last part just looks at the Value of ce, that is, of the current L-whatever, and colors it based on whether it's "Phase" or "Sub-Phase". If it's neither, it doesn't change the color.

What I hope is that by now you see what this does, and can tell me whether it's what you want it to do.

EC37 · #15 06-16-2014, 07:05 AM

OK, so here we go. See attached for example.

So, naturally, imagine when I perform the export it is the information, only.

I've updated my Timeline to identify by 'Phase' and 'Sub-Phase' rather than what it was previously, which was just the word 'header'.

So, we would delete the columns "Baseline Start", "Baseline Finish", and "Resource Names" (Columns E, H & J).
The Column "ID#" is non-existent when the export is performed, that is added by me.
Otherwise, how it is presented is exactly as I want it to look each week, but I want the macro to automatically color the selected cells in a row based on the information in the column titled "View Filter" (which ends up being Column L when the ID# column is added, and the others removed).

This should be much easier to work from- sorry I did not provide something sooner!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Formatting contents after Tab of continuous lines or formatting specific area of word	pawii	Word	1	05-12-2014 05:24 AM
macros	stebrownsword	Word VBA	0	08-28-2013 01:16 AM
How to do Formatting Using Macros	anju16saini	Word VBA	1	03-11-2013 04:15 AM
Formatting with macros	WaltR	Word VBA	8	05-15-2012 06:28 PM
Macros	nore	Outlook	0	06-01-2011 04:39 PM