Note: The other languages of the website are Google-translated. Back to English
登入  \/ 
x
or
x
註冊  \/ 
x

or

如何在多個單元格之間應用顏色漸變?

在Excel中,我們可以輕鬆地為一個單元格或多個單元格填充背景色,但是有時,我們需要按照下面的截圖所示填充漸變色,如何在Excel中獲得一個單元格或多個單元格中的顏色漸變?

顏色漸變到一個單元格 跨多個單元格的顏色漸變
doc顏色漸變1 doc顏色漸變2

使用“設置單元格格式”功能將漸變顏色應用於一個單元格

使用VBA代碼跨多個單元格應用漸變顏色


箭頭藍色右氣泡 使用“設置單元格格式”功能將漸變顏色應用於一個單元格

在Excel中,“設置單元格格式”功能可以幫助您在一個單元格中填充顏色漸變,請執行以下操作:

1。 選擇一個或多個要用每個單元的顏色漸變填充的單元,然後右鍵單擊以選擇 單元格格式 從上下文菜單的 單元格格式 對話框中的 標籤,請點擊 填充效果 按鈕,請參見屏幕截圖:

doc顏色漸變3

2。 在 填充效果 對話框中,從以下兩個下拉列表中選擇要使用的兩種顏色 顏色 部分,然後根據需要選擇一種陰影樣式,例如水平,垂直等。 看截圖:

doc顏色漸變4

3。 然後點擊 OK > OK 關閉對話框,並為每個單元格填充漸變顏色,如下所示:

doc顏色漸變5


箭頭藍色右氣泡 使用VBA代碼跨多個單元格應用漸變顏色

上面的方法可以幫助我們在單個單元格內進行顏色漸變,如果您需要在多個單元格之間遮蔽顏色漸變,則需要應用VBA代碼來解決。

1。 首先,為一系列單元格填充特定的背景色。

2。 按住 ALT + F11 鍵打開 Microsoft Visual Basic for Applications 窗口。

3。 點擊 插入 > 模塊,然後將以下代碼粘貼到 模塊 窗口。

VBA代碼:在多個單元格之間應用漸變顏色:

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。 然後按 F5 鍵運行此代碼,並出現一個提示框,提醒您選擇要填充漸變色的彩色單元格,請參見屏幕截圖:

 

doc顏色漸變6

5。 然後點擊 OK 按鈕,多個單元格中的顏色已顯示為漸變顏色,請參見屏幕截圖:

 

doc顏色漸變7


最佳辦公效率工具

Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%

  • 重用: 快速插入 複雜的公式,圖表 以及您以前使用過的任何東西; 加密單元 帶密碼 創建郵件列表 並發送電子郵件...
  • 超級公式欄 (輕鬆編輯多行文本和公式); 閱讀版式 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不會丟失數據; 拆分單元格內容; 合併重複的行/列...防止細胞重複; 比較範圍...
  • 選擇重複或唯一 行; 選擇空白行 (所有單元格都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇...
  • 確切的副本 多個單元格,無需更改公式參考; 自動創建參考 到多張紙; 插入項目符號,複選框等...
  • 提取文字,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級濾鏡 (將過濾方案保存並應用於其他工作表); 高級排序 按月/週/日,頻率及更多; 特殊過濾器 用粗體,斜體...
  • 結合工作簿和工作表; 根據關鍵列合併表; 將數據分割成多個工作表; 批量轉換xls,xlsx和PDF...
  • 超過300種強大功能。 支持Office / Excel 2007-2019和365。支持所有語言。 在您的企業或組織中輕鬆部署。 完整功能30天免費試用。 60天退款保證。
kte選項卡201905

Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 每天將您的工作效率提高50%,並減少數百次鼠標單擊!
officetab底部
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    MArcela CAmargo · 5 months ago
    Hello, How can I aply this formula for two colors, above I can't watch de solution. Please

  • To post as a guest, your comment is unpublished.
    Gordon · 8 months ago
    Hello, Can you apply this to a range like 1 to 5 , cells having a number in them, having the 5 as the darkest ?

    Would Kutools be able to do a little similar to above , but then arrange a row of numbers into a scale - not good at explaining. Say a survey answering on a scale of 1 to 5 , then graphical show one bar per question showing percent of answers in 1, 2 ,3 ,4 ,5 ; 5% 1's, 10% 2's 15% 3's and 50% 4's , 20% 5's but showing a gradient of colours in a horizontal bar (better if 2 colours).

    Thanks , Gord
  • To post as a guest, your comment is unpublished.
    Alan · 1 years ago
    Hi, I've tried copying the VBA code but when I try to run it I keep getting a message that says 'Compile Error: Invalid Outside Procedure'....


    How do I fix this??


    Thanks!
  • To post as a guest, your comment is unpublished.
    Jasmin · 2 years ago
    Hello, how can I go from yellow to red (for example)? It works only from White to an other color. I work with the code from left to right.
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      Hello, Jasmin,
      Sorry for that, this code only applied to one color, and if you want to fill gradient from left to tight, the below comment has the solution, please check it.
      Thank you!
      • To post as a guest, your comment is unpublished.
        Gord Thompson · 7 months ago
        Hello, Can you apply this to a range like 1 to 5 , cells having a number in them, having the 5 as the darkest ?

        Would Kutools be able to do a little similar to above , but then arrange a row of numbers into a scale - not good at explaining. Say a survey answering on a scale of 1 to 5 , then graphical show one bar per question showing percent of answers in 1, 2 ,3 ,4 ,5 ; 5% 1's, 10% 2's 15% 3's and 50% 4's , 20% 5's but showing a gradient of colours in a horizontal bar (better if 2 colours).

        Thanks , Gord
  • To post as a guest, your comment is unpublished.
    Piyaphan · 2 years ago
    Why I got Black-White color replace my gradient
    • To post as a guest, your comment is unpublished.
      Nicolas · 6 months ago
      Hello! I have the same problem, i choose the colors for my gradient but when applying the code it turns into black to white gradient. Anyy help?? thanks!
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      Hello, Piyaphan,
      The above code works well in my worksheet, which Excel version do you use?
      Or you can give your problem more detailed.
      Thank you!
  • To post as a guest, your comment is unpublished.
    paul · 3 years ago
    hey, I'm not that used to VBA codes. How do I set another color in the code?
    • To post as a guest, your comment is unpublished.
      skyyang · 3 years ago
      Hello, paul,
      If you want to set another color, you just need to fill your desired color to the cells, and then apply the above code in this article.
      Please try it.
      • To post as a guest, your comment is unpublished.
        paul · 3 years ago
        thanks! it works :)
  • To post as a guest, your comment is unpublished.
    Sean · 3 years ago
    How do I get this to have the gradient go left to right?
    • To post as a guest, your comment is unpublished.
      skyyang · 3 years ago
      Hello, Sean,
      To apply the color gradient from left to right, please use the following VBA code:

      Sub colorgradientmultiplecells()
      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.Columns.Count
      For K = 1 To xRg.Rows.Count
      xColor = xRg.Cells(K, 1).Interior.Color
      For I = xCount To 1 Step -1
      xRg.Cells(K, I).Interior.Color = xColor
      xRg.Cells(K, I).Interior.TintAndShade = (xCount - (I - 1)) / xCount
      Next
      Next
      End Sub

      Hope it can help you, thank you!
      • To post as a guest, your comment is unpublished.
        Ashley · 2 years ago
        How can I make the code to go it from right to left, Thanks in advance
        • To post as a guest, your comment is unpublished.
          skyyang · 2 years ago
          Hello, Ashley,
          To make the color gradient from right to left, the following vba code can help you, please try it.

          Sub colorgradientmultiplecells()
          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.Columns.Count
          For K = 1 To xRg.Rows.Count
          xColor = xRg.Cells(K, 1).Interior.Color
          For I = xCount To 1 Step -1
          xRg.Cells(K, I).Interior.Color = xColor
          xRg.Cells(K, I).Interior.TintAndShade = I / xCount
          Next
          Next
          End Sub
          • To post as a guest, your comment is unpublished.
            Kristina · 1 years ago
            Is it possible to perform this gradient but from bottom left to top right?
      • To post as a guest, your comment is unpublished.
        Sean · 2 years ago
        Is it possible to post a code that does gradient from top to bottom? I would really appreciate it.
        • To post as a guest, your comment is unpublished.
          Laura · 1 years ago
          I would need the code from the darkest shade at the top to the lighter shade at the bottom as well.. :(
          • To post as a guest, your comment is unpublished.
            skyyang · 1 years ago
            Hi, Laura,
            To sove your task, please apply the folloiwng code:

            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 = I / xCount
            Next
            Next
            End Sub

            Please try, hope it can help you!
      • To post as a guest, your comment is unpublished.
        Daisy · 3 years ago
        How would I do this if I wanted it top left to bottom right gradient?