Neat, huh? When you place your mouse over a cell, it changes color to show that you are selecting it. It’s a true “rollover”: no mouse-clicks required. In my last post, I show how that might be useful.
Wondering how I did it? Checkout this file:
Rollover Surprise.xlsm
Jordan GoldmeierCo-founder, Excel TVFormer Adjunct Instructor, Wake Forest University
Consultant, Anarchy Data · Instructor, Full Stack Modeller
Excel
Financial Modeling
Data Visualization
Analytics
Jordan Goldmeier is an accomplished data professional with a wealth of experience across various industries. He currently serves as a consultant at Anarchy Data, where he assists businesses in maximizing the capabilities of Excel for financial planning and analysis. Jordan is also an instructor at Full Stack Modeller and a former Adjunct Instructor in Analytics at Wake Forest University. His extensive career has seen him hold positions as the Chief Operations Officer at Excel.TV, Data Science Manager at DataKind, Data Scientist at Dealer Tire and EY, Analytics & Data Vis Developer at The Perduco Group, and Operations Research Analyst at Booz Allen Hamilton. Jordan's background in data analytics and his passion for Excel make him a valuable resource for businesses seeking to improve their data-driven decision-making processes.
Historical comments preserved from the WordPress archive. Commenting is no longer active.
Bob Phillips
Jordan,I have always preferred to use an overlaid shape to highlight rather than CF, so I adapted this technique to work with a shape. One big benefit is that it doesn't need any of the helper data.Just create a shape cell sized and fill it as required.Use a mouseover formula of=IFERROR(HYPERLINK(Rollover(ROW(),COLUMN()),""),"")and the the Rollover UDF looks likePublic Function Rollover(row As Integer, col As Integer) With ActiveSheet.Shapes("hilite") .Left = Cells(row, col).Left .Top = Cells(row, col).Top End With End Function
Bob Phillips
Those UDF arguments should be type Long not Integer.
Jordan
Hey Bob! Using a shape instead of conditional formatting definitely looks like it has some speed improvements on top of not having to make the helper table. Sometimes I use the rollover method in conjunction with allowing the user to click on a cell – usually handled through a worksheet_change event – but now I'm spinning my wheels on using a shape instead that will execute a macro when clicked.Anyway – it's an honor to have you post to my blog. One of these days I'll make it across the pond to an Excel conference. First round at the pub will be on me…. 😉
Bob Phillips
Jordan,I do hope you can make it, myself and Andy pope are quite frequent visitors to the pub :).Whilst I have used the Hyperlink function to allow changing other cells on a worksheet before, this rollover technique is a superb extension, I can't stop trying to shoe-horn it into previous applications. When I saw the periodic table I was so impressed I immediately sent it over to my daughter, a BioChemistry PhD student at Cantebury Uni in New Zealand. Great stuff! Don't you just love Excel?
Jordan
Excel IS great! I think using a shape to handle a click (the shape would have to be semi-opaque) may prove a better technique in some circumstances. For example, because I use the selection_Change event for the periodic table, clicking on an already selected filter won't immediately turn it off because, well, the selection hasn't changed – no macro is fired.But I'm sure glad to hear you are trying to incorporate the rollover into your work. I'd love to take a look at anything you can send me. My hope too (perhaps a pipe dream), is that Microsoft will see this (really, I'm just a nobody blog – I'm relying on the big ones to spread the message) and consider creating a more formal method to provide both a mechanism for "highlighting" and "details on demand." As it stands, using the hyperlink formula isn't terribly intuitive and is, at times, a bit cumbersome. I can't help but think I'm simply taking an advantage of a bug in Excel by using something in way that it was not intended.
Bob Phillips
Yes, the transparency level of the fill colour iin the shape needs to be at least 50%, probably closer to 75%, in order to see any cell contents – getting close to glass windows here :).I have played a little with it and created a generic row/column highlighter. I had one already using CF, but I adapted it to this technique. It allows specifying a range that the highlighting is contained within, for example to keep it within a table, whole rows and columns, or neither which just highlights the moused over cell. ALl of this is configurable to each sheet or the whole workbook.I agree though, it is pushing at a door that was probably inadvertently left open. Unfortunately, MS's current approach seems to lock down more than open up. It must be possible, they are very big on the concept of live preview, look how much has been built into Excel 13.
Comments (6)
Historical comments preserved from the WordPress archive. Commenting is no longer active.
Jordan,I have always preferred to use an overlaid shape to highlight rather than CF, so I adapted this technique to work with a shape. One big benefit is that it doesn't need any of the helper data.Just create a shape cell sized and fill it as required.Use a mouseover formula of=IFERROR(HYPERLINK(Rollover(ROW(),COLUMN()),""),"")and the the Rollover UDF looks likePublic Function Rollover(row As Integer, col As Integer) With ActiveSheet.Shapes("hilite") .Left = Cells(row, col).Left .Top = Cells(row, col).Top End With End Function
Those UDF arguments should be type Long not Integer.
Hey Bob! Using a shape instead of conditional formatting definitely looks like it has some speed improvements on top of not having to make the helper table. Sometimes I use the rollover method in conjunction with allowing the user to click on a cell – usually handled through a worksheet_change event – but now I'm spinning my wheels on using a shape instead that will execute a macro when clicked.Anyway – it's an honor to have you post to my blog. One of these days I'll make it across the pond to an Excel conference. First round at the pub will be on me…. 😉
Jordan,I do hope you can make it, myself and Andy pope are quite frequent visitors to the pub :).Whilst I have used the Hyperlink function to allow changing other cells on a worksheet before, this rollover technique is a superb extension, I can't stop trying to shoe-horn it into previous applications. When I saw the periodic table I was so impressed I immediately sent it over to my daughter, a BioChemistry PhD student at Cantebury Uni in New Zealand. Great stuff! Don't you just love Excel?
Excel IS great! I think using a shape to handle a click (the shape would have to be semi-opaque) may prove a better technique in some circumstances. For example, because I use the selection_Change event for the periodic table, clicking on an already selected filter won't immediately turn it off because, well, the selection hasn't changed – no macro is fired.But I'm sure glad to hear you are trying to incorporate the rollover into your work. I'd love to take a look at anything you can send me. My hope too (perhaps a pipe dream), is that Microsoft will see this (really, I'm just a nobody blog – I'm relying on the big ones to spread the message) and consider creating a more formal method to provide both a mechanism for "highlighting" and "details on demand." As it stands, using the hyperlink formula isn't terribly intuitive and is, at times, a bit cumbersome. I can't help but think I'm simply taking an advantage of a bug in Excel by using something in way that it was not intended.
Yes, the transparency level of the fill colour iin the shape needs to be at least 50%, probably closer to 75%, in order to see any cell contents – getting close to glass windows here :).I have played a little with it and created a generic row/column highlighter. I had one already using CF, but I adapted it to this technique. It allows specifying a range that the highlighting is contained within, for example to keep it within a table, whole rows and columns, or neither which just highlights the moused over cell. ALl of this is configurable to each sheet or the whole workbook.I agree though, it is pushing at a door that was probably inadvertently left open. Unfortunately, MS's current approach seems to lock down more than open up. It must be possible, they are very big on the concept of live preview, look how much has been built into Excel 13.