CODEWITHSUNDEEP
excel
Buras
Buras

Reputation: 3099

How to assign gradient colors to an excel column?

I need to assign different colors to cells in my Excel column, such that the first cell is white, the second is a little bit darker, the third is darker than the previous one etc. Here is the link to a .png file: https://dl.dropboxusercontent.com/u/41007907/gradientColumn.png

How can I do this fast? Is there any shortcut command?

Upvotes: 1

Views: 11663

Answers (1)

David Zemens
David Zemens

Reputation: 53623

If you're looking for a VBA-solution, use the cell's .Interior.TintAndShade property.

Here is a quick macro that you can use which calculates a gradient fill based on the number of cells in the column. This should apply an even gradient, e.g.:

gradient fill cells

Sub Macro3()

Dim firstCell As Range 'the first cell, and the cell whose color will be used for all others.
Dim cellColor As Long 'the cell color that you will use, based on firstCell
Dim allCells As Range 'all cells in the column you want to color
Dim c As Long  'cell counter
Dim tintFactor As Double 'computed factor based on # of cells.

Set firstCell = Range("A1")
cellColor = firstCell.Interior.Color

Set allCells = Range("A1:A10")

For c = allCells.Cells.Count To 1 Step -1
    allCells(c).Interior.Color = cellColor
    allCells(c).Interior.TintAndShade = _
        (allCells.Cells.Count - (c - 1)) / allCells.Cells.Count

Next


End Sub

Edited to fill gradient from light-to-dark. If you prefer dark to light, then do:

allCells(c).Interior.TintAndShade = _
        (c-1) / allCells.Cells.Count

Upvotes: 7

Related Questions