Excel VBA Real-time highlighter

as i deal with spreadsheets where the data constantly changes, i need a help with VBA solution to highlight any cell that changes meaning if increases value then green and if decreases values then red. just to blink.

I found commercial Add-In, but it requires administrative password to install and in our office environment we have domain user that cannot install exe files, besides corporate does not allow third part commercial softwares.  so only VBA works for me.

A brief description of Spheresoft Highlighter  from the net is below, i do not need all the sophistications, all I need two colours, and time interval 2 seconds for blinks.


-------------
Real-time highlighting is now available for users of Microsoft Excel. The Spheresoft Highlighter lets users specify that cells in their spreadsheets automatically highlight when values change in real-time.
The Highlighter is very useful for financial professionals who incorporate real-time market data feeds. Also is useful for anyone who creates complex spreadsheet models and wants to see what the changes in input can affect.
· Highlighter works on cells that change as a result of real-time DDE links, OLE automation, calculations, or direct user input
· Highlights can flash/blink for a specified time, or stay colored until the spreadsheet changes again or are cleared by the user
· Separate highlight colors for up-ticks and down-ticks
· User-customization of highlight colors and the length of the highlight

------------------
LVL 6
FloraAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Try the attached. If you change any cell on the "Main" sheet the cell will flash either green or red. The code could be easily change so that only certain ranges are affected. The sheet "hidden" (which in production should be hidden and probably password protected) is used to tell if a cell on Main has changed, and in what direction.
Q-28628160.xlsm
FloraAuthor Commented:
Martin,

this is perfect thanks very much.

how can the  code be modified that instead of font color blinking, it would be the cell background color.

i tried modifying code with changing .Font  to .Interior  it works, except one problem, when the blinking is done, then the cell background color remain white in there, instead of No Fill. becuase i can see that the gridline of modified cell, is not showing.

in addition, when i want to delete entries from sheet, VBA gives me error.

thanks again Martin.
Martin LissOlder than dirtCommented:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

FloraAuthor Commented:
Thank you very much Martin.

this is brilliant.

i am sorry, i could not get back earlier, as you know it is  Flu season and i was sick.


hope you are doing fine.
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help, and I'm still vertical, so that's good:)

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
FloraAuthor Commented:
thanks Martin.

i like the article using Enums and i voted for it.
Martin LissOlder than dirtCommented:
Thank you, I personally find them very useful.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.