Change cells from night to day in Excel – How to

Change cells from night to day in Excel – How to

Change cells from night to day in Excel - How to

This quick video tip shows you how to change cells from night to day in Excel.

Change cells from night to day in Excel

In this instance, we use a macro to accomplish this task. So, what we do is record the process of selecting all the cells in the sheet. Then, we change the color of all the cells to black. We will consider that night.

When we switch the cells to white, it also hides the gridlines. In the case of day, we want to remove the black shading, and just show the cells without color. Therefore, we could see the gridlines. Therefore, we had to run another macro to see how Excel did it.

After we ran both macros, we changed the code or took from the macros what we needed. As you may notice, there is no need to select all the cells before applying a color. In fact, we did both macros in a single line.

The video

The code

Below is the code to turn all cells in Excel black, from the macro recorder.

Sub Macro3()
' Macro3 Macro

    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

We want to convert the above code to one line of code. Thus, this is the code we did below.

Sub Night()
    Cells.Interior.Color = RGB(0, 0, 0)
End Sub

So, we took Cells and Interior from the macro code, and added the Color property.

In Excel, RGB stands for red, green, and blue. The lowest value for each color code is 0. And, the highest value is 255. All zeros will yield black. While, all 255’s will yield white.

Here is the code for Day.

Sub Day()
    Cells.Interior.Pattern = xlNone
End Sub

Instead of using the Color property, we simply change the pattern to none, with the Pattern property.

After we have our macros set, we can produce our buttons. And, there you have it. Enjoy!

Addition thoughts

Next thing you can do, is use just one button to change from night to day. Basically, when you click on the button, the text changes based on the color of the cells. Overall, it will be like a toggle button. That would be like a nice upgrade.


Education home  Fun with macros  Excel Effects home