How to apply color gradient across multiple cells?
In Excel, we can easily fill background color to a cell or multiple cells, but, sometimes, we need the color be filled gradient as following screenshot shown, how could get the color gradient in a cell or across multiple cells in Excel?
| Color gradient to one cell | Color gradient across to multiple cells |
 |
 |
Apply gradient color to one cell with Format Cells feature
Apply gradient color across multiple cells with VBA code
Apply gradient color to one cell with Format Cells feature
In Excel, the Format Cells feature can help you to fill the color gradient in one cell, please do as follows:
1. Select a cell or multiple cells that you want to be filled with color gradient each cell, and then right click to choose Format Cells from the context menu, in the Format Cells dialog box, under the Fill tab, please click Fill Effects button, see screenshot:
2. In the Fill Effects dialog, select two colors that you want to use from the two drop down lists of Colors section, and then select one shading styles as you like, such as horizontal, Vertical, and so on. See screenshot:
3. Then click OK > OK to close the dialogs, and the gradient color is filled for each cell as following screenshot shown:
Apply gradient color across multiple cells with VBA code
The above method can help us to color gradient within individual cell, if you need to shade the color gradient across multiple cells, you need apply a VBA code to solve it.
1. First, fill a specific background color to a range of cells.
2. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
3. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Apply gradient color across multiple cells:
Sub colorgradientmultiplecells()
'Updateby Extendoffcie
Dim xRg As Range
Dim xTxt As String
Dim xCell As Range
Dim xColor As Long
Dim I As Long
Dim K As Long
Dim xCount As Long
On Error Resume Next
If ActiveWindow.RangeSelection.Count > 1 Then
xTxt = ActiveWindow.RangeSelection.AddressLocal
Else
xTxt = ActiveSheet.UsedRange.AddressLocal
End If
LInput:
Set xRg = Application.InputBox("please select the cells range:", "Kutools for Excel", xTxt, , , , , 8)
If xRg Is Nothing Then Exit Sub
If xRg.Areas.Count > 1 Then
MsgBox "does not support multiple selections", vbInformation, "Kutools for Excel"
GoTo LInput
End If
On Error Resume Next
Application.ScreenUpdating = False
xCount = xRg.Rows.Count
For K = 1 To xRg.Columns.Count
xColor = xRg.Cells(1, K).Interior.Color
For I = xCount To 1 Step -1
xRg.Cells(I, K).Interior.Color = xColor
xRg.Cells(I, K).Interior.TintAndShade = (xCount - (I - 1)) / xCount
Next
Next
End Sub
4. Then press F5 key to run this code, and a prompt box is appeared to remind you selecting the colored cells that you want to fill gradient color, see screenshot:
5. And then click OK button, the color within multiple cells has displayed as gradient color, see screenshot:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
