Microsoft Office Forums - View Single Post - NEWB to Macros

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