Lesson#79: Automatic changing Excel sheet color every second

Lesson#79: Automatic changing Excel sheet color every second

Let me show you how to make a spreadsheet that changes its color every second automatically. to make this you have to follow some steps where I will apply a little VBA to make it really automatic.

1. Click the upper left corner of the sheet to select all of the spreadsheets.

Automatic changing Excel sheet color in every second

2. Go to Conditional Formatting>New Rule>Use a formula to determine which cells to format
3. Put the formula =MOD(SECOND(NOW()),5)=0 and select a format color

Like that, I have added different rules with four different formulas

=MOD(SECOND(NOW()),5)=1
=MOD(SECOND(NOW()),5)=2
=MOD(SECOND(NOW()),5)=3
=MOD(SECOND(NOW()),5)=4

Automatic changing Excel sheet color in every second

4. Click Apply and OK. Now for making it automatic I will apply some VBA.

5. From the Developer Tab I have inserted a Button (Form Control) and clicked on New in the Assign Macro window.

6. Opening the VBA tab I added this code

Sub Button2_Click()
Call refreshcell
End Sub
Sub refreshcell()Range(“a1”).CalculateApplication.OnTime Now + TimeValue(“00:00:1”), “refreshcell”
End Sub

7. Edited the button text and the size just looks good.

8. Save this sheet as a macro-enabled workbook in .xlsm format.

Now see every second this sheet is auto-changing its color. I put that button to call the function refreshcell()
if the sheet doesn’t start to refresh itself automatically after opening it.

I have given the download link for the file. Click Here to download.

See also  Lesson#39: Cool tricks to make automatically updating Gantt chart

Hi! I am Puspendu. I am the founder and author of Excelabcd. I am little creative person, blogger and Excel-maniac guy. I hope you enjoy my blog.

Leave a Reply

Your email address will not be published. Required fields are marked *

*