NEWB to Macros

EC37 · #1 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 · #2 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 · #3 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?

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