#1
|
|||
|
|||
NEWB to Macros - formatting exports
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 |
#2
|
||||
|
||||
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. |
#3
|
|||
|
|||
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!
|
#4
|
|||
|
|||
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 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 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 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. Last edited by macropod; 06-16-2014 at 06:49 PM. Reason: Added code tags & formatting |
#5
|
||||
|
||||
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 Code:
Range("E:E,G:G,J:J").Delete Shift:=xlToLeft Now let's take a look at the next section.... |
#6
|
||||
|
||||
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 |
#7
|
|||
|
|||
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. |
#8
|
||||
|
||||
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 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. |
#9
|
|||
|
|||
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? |
#10
|
||||
|
||||
"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. |
#11
|
|||
|
|||
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...
|
#12
|
||||
|
||||
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? |
#13
|
|||
|
|||
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 |
#14
|
||||
|
||||
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 Code:
Sub Main() . . . End Sub Code:
Range("E:E,G:G,J:J").Delete Shift:=xlToLeft Code:
For jr = 1 To 264 Set ce = Range("L" & jr) . . . Next jr 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 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. |
#15
|
|||
|
|||
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 |