June 20

How to: highlighting cells using the rollover technique in Excel

6  comments

In my last post I used my patented rollover technique to create an effect similar to the one shown below:

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 Goldmeier
Latest posts by Jordan Goldmeier (see all)

Tags

optionexplicitvba


You may also like

April 9, 2011

How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell

October 26, 2014

Monte Carlo Simulation Formula in Excel – Tutorial and Download

September 17, 2012

The Excel Rollover Mini FAQ
  • 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

  • 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.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Free 24 Excel Tips Guide

    Discover the secrets to Excel efficiency with our free guide: '24 Proven Microsoft Excel Tips to Help You Save Time'

    A humble gift from us to you, to make your Excel journey smoother and more productive. Grab your copy now and start mastering Excel like never before!

    >