Note: The other languages of the website are Google-translated. Back to English
Logáil isteach  \/ 
x
or
x
Cláraigh  \/ 
x

or

Conas grádán datha a chur i bhfeidhm ar fud ilchealla?

In Excel, is féidir linn dath cúlra a líonadh go héasca i gcill nó i gcealla iolracha, ach, uaireanta, teastaíonn an grádán líonta dathanna uainn mar a thaispeántar an pictiúr a leanas, conas a d’fhéadfaimis an grádán datha a fháil i gcill nó trasna ilchealla in Excel?

Grádán datha go cill amháin Grádán datha trasna go cealla iolracha
grádán datha doc 1 grádán datha doc 2

Cuir dath grádáin i bhfeidhm ar chill amháin le gné Cealla Formáid

Cuir dath grádáin i bhfeidhm ar fud ilchealla le cód VBA


mboilgeog cheart gorm saighead Cuir dath grádáin i bhfeidhm ar chill amháin le gné Cealla Formáid

In Excel, is féidir leis an ngné Cealla Formáid cabhrú leat an grádán datha a líonadh i gcill amháin, déan mar a leanas le do thoil:

1. Roghnaigh cill nó ilchealla ar mhaith leat a bheith líonta le grádán datha gach cill, agus ansin cliceáil ar dheis chun roghnú Cealla Formáid ón roghchlár comhthéacs, sa Cealla Formáid bosca dialóige, faoin Líon cluaisín, cliceáil le do thoil Éifeachtaí Líonta cnaipe, féach ar an scáileán:

grádán datha doc 3

2. Sa an Éifeachtaí Líonta dialóg, roghnaigh dhá dhath is mian leat a úsáid ón dá liosta anuas de Dathanna roinn, agus ansin roghnaigh stíleanna scáthú amháin mar is mian leat, mar shampla cothrománach, Ingearach, agus mar sin de. Féach an pictiúr:

grádán datha doc 4

3. Ansin cliceáil OK > OK chun na dialóga a dhúnadh, agus líontar an dath grádáin do gach cill mar a thaispeántar an scáileán a leanas:

grádán datha doc 5


mboilgeog cheart gorm saighead Cuir dath grádáin i bhfeidhm ar fud ilchealla le cód VBA

Is féidir leis an modh thuas cabhrú linn grádán a dhathú laistigh de chill aonair, más gá duit an grádán datha a scáthú ar fud ilchealla, ní mór duit cód VBA a chur i bhfeidhm chun é a réiteach.

1. Ar dtús, líon dath cúlra ar leith chuig raon cealla.

2. Coinnigh síos an ALT + F11 eochracha a oscailt Microsoft Visual Basic d’Fheidhmchláir fhuinneog.

3. Cliceáil Ionsáigh > Modúil, agus greamaigh an cód seo a leanas sa Modúil Fuinneog.

Cód VBA: Cuir dath grádáin i bhfeidhm ar fud ilchealla:

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. Ansin brúigh F5 eochair chun an cód seo a rith, agus feictear bosca pras le cur i gcuimhne duit na cealla daite a theastaíonn uait dath grádáin a líonadh a fheiceáil, féach an scáileán:

 

grádán datha doc 6

5. Agus ansin cliceáil OK cnaipe, tá an dath laistigh de ilchealla ar taispeáint mar dhath grádáin, féach an scáileán:

 

grádán datha doc 7


Na hUirlisí Táirgiúlachta Oifige is Fearr

Réitíonn Kutools for Excel an chuid is mó de do chuid Fadhbanna, agus Méadaíonn sé do Tháirgiúlacht 80%

  • Athúsáid: Cuir isteach go tapa foirmlí casta, cairteacha agus aon rud a d'úsáid tú roimhe seo; Cealla a Chriptiú le pasfhocal; Cruthaigh Liosta Ríomhphoist agus seol ríomhphoist ...
  • Barra Foirmle Super (cuir línte iolracha téacs agus foirmle in eagar go héasca); Leagan Amach Léitheoireachta (líon mór cealla a léamh agus a chur in eagar go héasca); Greamaigh go dtí an Raon Scagtha...
  • Cumaisc Cealla / Sraitheanna / Colúin gan Sonraí a chailleadh; Ábhar Cealla Scoilt; Comhcheangail Sraitheanna / Colúin Dúblacha... Cill Dúblach a Chosc; Déan comparáid idir Ranganna...
  • Roghnaigh Dúblach nó Uathúil Sraitheanna; Roghnaigh Blank Rows (tá na cealla uile folamh); Aimsigh Super agus Fuzzy Aimsigh i go leor Leabhar Oibre; Roghnaigh go randamach ...
  • Cóip Díreach Cealla Il gan tagairt fhoirmle a athrú; Tagairtí Cruthaigh Auto chuig Bileoga Il; Cuir Urchair isteach, Boscaí Seiceála agus go leor eile ...
  • Sliocht Téacs, Cuir Téacs leis, Bain de réir Poist, Bain Spás; Subtotals Paging a chruthú agus a phriontáil; Tiontaigh Idir Ábhar Cealla agus Tráchtanna...
  • Scagaire Super (scéimeanna scagaire a shábháil agus a chur i bhfeidhm ar bhileoga eile); Ard-Sórtáil de réir míosa / seachtaine / lae, minicíocht agus níos mó; Scagaire Speisialta le cló trom, iodálach ...
  • Comhcheangail Leabhair Oibre agus Bileoga Oibre; Cumaisc Táblaí bunaithe ar eochaircholúin; Roinn Sonraí i Ilbhileoga; Baisc Tiontaigh xls, xlsx agus PDF...
  • Níos mó ná 300 gné chumhachtach. Tacaíochtaí Office / Excel 2007-2019 agus 365. Tacaíonn sé le gach teanga. Imscaradh éasca i d’fhiontar nó d’eagraíocht. Gnéithe iomlána triail saor in aisce 30 lá. Ráthaíocht ar ais airgid 60 lá.
cluaisín kte 201905

Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
bun 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?