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

USAMax

Well-known Member
Joined
May 31, 2006
Messages
843
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
You could generate it with something like this:

Code:
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
 
Upvote 0
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.

Code:
    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.
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top