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

USAMax

Well-known Member
Joined
May 31, 2006
Messages
832
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!
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

USAMax

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

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

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

Watch MrExcel Video

Forum statistics

Threads
1,123,295
Messages
5,600,789
Members
414,405
Latest member
Zaurb

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
Top