2 Effective Methods to Fill Your Excel Worksheet with Gradient Color

If you can use gradient color in your work, you are sure to improve your work efficiency. In this article, we will introduce two methods to fill your worksheet with gradient color.

It is very easy for you to fill a cell or a range with a certain color. Besides, you can also fill gradient color in a cell. However, there is no direct way in Excel to fill a range with gradient color. Now we have found two methods to finish this task.

Method 1: Insert Background

In this method, you can insert an image with gradient color as the background.

Find an image with the gradient color that you like. Or you can also make such an image in your computer.
Now open the target Excel that you need to fill gradient color.
In the worksheet, click the tab “Page Layout” in the ribbon.
And then click the button “Background” in the toolbar.
After that, choose the path that contains the target image.
Next select this image.
And then click the button “Insert” in the window.

Thus, the image has become the background of this worksheet.

Using this method is very easy. But on the other hand, you need to adjust the image size, so that you can fit the cell. Otherwise, there will be two different colors in one cell like the image show. This is actually the border of the background image.

Method 2: Use VBA Macro

Here we will introduce another method. And you will need to use Excel VBA to finish this task. Before you fill the gradient color, you need to choose the two different colors for the worksheet. What’s more, you also need to decide the RGB numbers of two colors for the gradient color. In this example, we will use the two color: RGB(125,205,242) and RGB(215,239,228).

Press the button “Alt +F11” on the keyboard. Thus, you can open the Visual Basic editor.
In this editor, insert a new module.
After that, copy the following codes into the module:

Sub FillGradientColorInRange()
  Dim nTargetRowCount As Integer

  For nTargetRowCount = 1 To 100
    Range("A" & nTargetRowCount, "E" & nTargetRowCount).Interior.Color = RGB( _
      Round((215 - ((215 - 125) / 99) * (nTargetRowCount - 1)), 0), _
      Round((239 - ((239 - 205) / 99) * (nTargetRowCount - 1)), 0), _
      Round((228 + ((242 - 228) / 99) * (nTargetRowCount - 1)), 0))
  Next nTargetRowCount
End Sub

In this example, we will fill the range A1:E100. And the calculation method is shown in the codes. Besides, for different color parameters, you need to adjust the formula according to the actual numbers. Because the color parameters are all integer, we will also round the numbers in case there exist errors.

And then run this macro. You can press the button “F5” on the keyboard to run it. On the other hand, you can also click the button “Run Sub”.
After that, come back to the worksheet. We have zoomed out the worksheet so that you can see clearly. The target range is filled with gradient color.

When you need to fill other range with gradient color, you need to modify the codes and run it again.

A Comparison of the Two Methods

You can see that both methods are very effective. But sometimes you will not know which method you can use. In the table below, we have listed all the advantages and disadvantages of the two methods that we can think of.

Comparison

Insert Background

Use VBA Macro

Advantages

1. If you are not familiar with Excel VBA, this method is your choice.

2. The whole worksheet will be filled with the gradient color by this method.

1. Just by one click or one press, you can get the result immediately.

2. This method will not produce unsuitable borders with two different colors.

Disadvantages

1. You need to spend time to find an image or make an image with gradient color.

2. In the border of the size of the background, there will be obvious boundaries.

3. The color is in background. When you fill a cell with another color, this color will cover the background.

1. When you need to modify the codes, you will easily make mistakes.

2. Only a target range will be filled with gradient color. You still need to modify the codes for other ranges if you have needs.

3. If you use VBA to fill a small range, the color transition will be not so natural.

Now you have a deeper understand of the two methods. And the next time you will know which method you can use in your actual worksheet.

Keep a Safe Environment for Your Excel Files

The environment is also very important for your Excel files. If the environment is not safe, you cannot make sure that Excel files will always be safe. However, even you have prepared a lot, you still have the chance to meet with Excel corruption. Therefore, you can prepare a third-party beforehand. Once your Excel corrupts, you can use this tool to repair corrupt xlsx data immediately.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Word doc document corruption and outlook repair software products. For more information visit www.datanumen.com