NEWB to Macros - formatting exports

[BobBridges]

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 (eg

"A"jr":L"jr

In VBA that must be

"A" & jr & ":L" & jr

...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:

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

(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.