Microsoft Office Forums - View Single Post - NEWB to Macros

BobBridges · 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.