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.
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
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
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.
Leave a Reply