asked on

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

------------------

Martin Liss

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

Flora Edwards

ASKER

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.

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Flora Edwards

ASKER

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 Liss

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

Flora Edwards

ASKER

thanks Martin.

i like the article using Enums and i voted for it.

Martin Liss

Thank you, I personally find them very useful.