Can you use a fill effect with a gradient effect over several cells?

[USAMax]

I have an impossible worksheet that I am working on for a client with 150 columns and growing. There are three sections with 34 columns each that are duplicate and need to be made to stand out so I want a gradient effect going from white in the left cell to a medium blue on the right. This company is all about aesthetics so appearance is everything so changing the color of each cell will be very helpful, a gradient effect will be preferred.

Thank you!

 

[Scott Huish]

You could generate it with something like this:

Sub test()
Dim x As Long, r As Long, g As Long, b As Long
r = 255: g = 255: b = 255
For x = 1 To 34
    Cells(1, x).Interior.Color = RGB(r, g, b)
    r = r - 4: g = g - 3.5: b = b - 1
Next
End Sub

 

[USAMax]

Now that is a brilliant idea Scott!

But to make it really work I would have to have it shade:
A1) The left cell from the darkest color to a dark medium shade of the color.
B1) The next cell from to a dark medium shade of the color to a medium shade.
C1) The next cell from a medium shade to a light medium shade and...
D1) The last cell from a light medium shade to white.

If I could do this it would make a lot more impact but how can I do that. Here is what the code looks like going from the darkest color to white.

    With Selection.Interior
        .Pattern = xlPatternLinearGradient
        .Gradient.Degree = 180
        .Gradient.ColorStops.Clear
    End With
    With Selection.Interior.Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.Interior.Gradient.ColorStops.Add(1)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.349009674367504
    End With
End Sub

I am only working with Grey here so I assume the TintAndShade of Zero is white so the left cell would have to end with Zero, but I don't understand the ColorStops.

 

[Scott Huish]

I think I understand the ColorStops. Don't know if you have used Photoshop, but I believe they are the different colors in the gradient. So if you went from Blue to White, there would be 2 ColorStops (Blue and White). If you went from Blue to Red and then to White, there would be 3 ColorStops, etc.

My next question is that you originally said you had 34 columns that you wanted this gradient effect on, but in your latest post, you had 4. Does the cycle need to continue over 34 columns or do you need 34 distinct blue gradients?

 

[USAMax]

I'm bad... I decided to take it to a simple section with 4 columns to see if I could get it to work first. If I use a For Next I can just change the variable to the number of columns latter.

That was my guess for the ColorStops too but I am not sure about the use of the command.

For now, I am just working with 4 columns.

Thanks Scott!