Hur applicerar jag färggradient över flera celler?

I Excel kan vi enkelt fylla bakgrundsfärg till en cell eller flera celler, men ibland behöver vi färgen fyllas lutning som följande skärmdump visas, hur kan färggradienten i en cell eller över flera celler i Excel?

Färggradient till en cell	Färggradient över till flera celler

Applicera gradientfärg på en cell med funktionen Formatera celler

Applicera gradientfärg över flera celler med VBA-kod

Applicera gradientfärg på en cell med funktionen Formatera celler

I Excel kan Format Cells-funktionen hjälpa dig att fylla färggradienten i en cell, gör så här:

1. Välj en cell eller flera celler som du vill fylla med färggradient för varje cell och högerklicka för att välja Formatera celler från snabbmenyn, i Formatera celler under dialogrutan Fyll klicka på fliken Fyllningseffekter knapp, se skärmdump:

doc färggradient 3

2. I Fyllningseffekter Välj två färger som du vill använda i de två nedrullningsbara listorna Färg och välj sedan en skuggstilar som du vill, till exempel horisontell, Vertikal och så vidare. Se skärmdump:

doc färggradient 4

3. Klicka sedan OK > OK för att stänga dialogerna och tonfärgen fylls för varje cell enligt följande skärmdump:

doc färggradient 5

Applicera gradientfärg över flera celler med VBA-kod

Ovanstående metod kan hjälpa oss att färga lutningen inom enskilda celler. Om du behöver skugga färggradienten över flera celler måste du tillämpa en VBA-kod för att lösa den.

1. Fyll först en specifik bakgrundsfärg i ett cellområde.

2. Håll ner ALT + F11 nycklar för att öppna Microsoft Visual Basic för applikationer fönster.

3. Klicka Insert > Modulernaoch klistra in följande kod i Modulerna Fönster.

VBA-kod: Applicera gradientfärg över flera celler:

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. Tryck sedan på F5 nyckel för att köra den här koden och en snabbruta visas för att påminna dig om att välja de färgade cellerna som du vill fylla övertoningsfärg, se skärmdump:

doc färggradient 6

5. Och klicka sedan på OK -knappen har färgen i flera celler visats som lutningsfärg, se skärmdump:

doc färggradient 7

De bästa Office-produktivitetsverktygen

Kutools för Excel löser de flesta av dina problem och ökar din produktivitet med 80%

återanvändning: Sätt snabbt i komplexa formler, diagram och allt som du har använt tidigare; Kryptera celler med lösenord; Skapa e-postlista och skicka e-post ...
Super Formula Bar (enkelt redigera flera rader med text och formel); Läslayout (enkelt läsa och redigera ett stort antal celler); Klistra in i filtrerat intervall...
Sammanfoga celler / rader / kolumner utan att förlora data; Delat cellinnehåll; Kombinera duplicerade rader / kolumner... Förhindra duplicerade celler; Jämför intervall...
Välj Duplicera eller Unikt Rader; Välj tomma rader (alla celler är tomma); Super Find och Fuzzy Find i många arbetsböcker; Slumpmässigt val ...
Exakt kopia Flera celler utan att ändra formelreferens; Skapa referenser automatiskt till flera ark; Sätt in kulor, Kryssrutor och mer ...
Extrahera text, Lägg till text, ta bort efter position, Ta bort mellanslag; Skapa och skriva ut personsökningstalsatser; Konvertera mellan celler innehåll och kommentarer...
Superfilter (spara och tillämpa filterscheman på andra ark); Avancerad sortering efter månad / vecka / dag, frekvens och mer; Specialfilter av fet, kursiv ...
Kombinera arbetsböcker och arbetsblad; Sammanfoga tabeller baserat på nyckelkolumner; Dela data i flera ark; Batchkonvertera xls, xlsx och PDF...
Mer än 300 kraftfulla funktioner. Stöder Office / Excel 2007-2019 och 365. Stöder alla språk. Enkel distribution i ditt företag eller organisation. Fullständiga funktioner 30-dagars gratis provperiod. 60-dagars pengarna tillbaka-garanti.

Läs mer... Gratis nedladdning... Köp...

Fliken Office ger ett flikgränssnitt till Office och gör ditt arbete mycket enklare

Aktivera flikredigering och läsning i Word, Excel, PowerPoint, Publisher, Access, Visio och Project.
Öppna och skapa flera dokument i nya flikar i samma fönster, snarare än i nya fönster.
Ökar din produktivitet med 50% och minskar hundratals musklick åt dig varje dag!

Läs mer... Gratis nedladdning... Köp...

21 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

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 · 6 months ago
Hello, How can I aply this formula for two colors, above I can't watch de solution. Please
- Reply
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
- Reply
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!
- Reply
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.
- Reply
- 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!
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Gord Thompson · 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
  
  Reply
To post as a guest, your comment is unpublished.
Piyaphan · 2 years ago
Why I got Black-White color replace my gradient
- Reply
- 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!
  
  Reply
- 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!
  
  Reply
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?
- Reply
- 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.
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  paul · 3 years ago
  
  thanks! it works :)
  
  Reply
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?
- Reply
- 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!
  
  Reply
  
  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
  
  Reply
  
  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
  
  Reply
  
  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?
  
  Reply
  
  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.
  
  Reply
  
  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.. :(
  
  Reply
  
  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!
  
  Reply
  
  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?
  
  Reply

Logga in på ditt konto

Återställning av lösenord