June 20

How to: highlighting cells using the rollover technique in Excel


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



You may also like

January 31, 2023

The Ultimate Guide to Excel Ribbon: Unlock its Hidden Features

February 17, 2022

Edit Excel Formulas in WORD – Spreadsheet Tips and Tricks

February 8, 2021

51: Oz du Soleil & the Global Excel Summit 2021

January 29, 2021

Global Excel Summit 2021

January 22, 2021

50: Randy Austin – Excel for Freelancers

January 8, 2021

49: Theresa Estrada – Microsoft Principal Program Manager Lead
  • 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 Power User Quick Guide

    Discover The BEST Ways To Use Lookups And Conditional Calculations Quickly And Easily With This Reference Guide -- You Won’t Want This To Leave Your Side

    With so many ways to use Excel, it can be difficult to memorize all of the key functions, calculations, and techniques you can employ to meet your goal: simply get the job done.