Apparently, Excel doesn't have a hover (mouse over) event support for cells. However, we can use the HYPERLINK function to mimic this functionality, thanks to mouse-over support for hyperlinks in Excel. In this guide, we’re going to show you how to trigger a macro by hovering over a cell in Excel.
- Create a user defined function (UDF) which includes the codes or macro you want to run.
Public Function MouseOver() ChangeColor (ActiveSheet.Range("E4")) End Function
- Enter the following formula according to your UDF's name.
- It's OK to see the #VALUE! error since we do not supply a valid path. You can always use the IFERROR function to display a friendly text.
You will see the effect when you hover your mouse cursor on your cell. Our code simple toggles the target cell's (E4) color.
You can combine this functionality with conditional formatting as well. For example, you can build a fancy multiselect tool instead of a common dropdown or option buttons.
The logic behind the tool is simple. You need a UDF that returns a value resembles the hovered-on item. To make this, our UDF accepts a range argument which contains the item's name and send the name into a helper cell (E6).
Public Function HighlightMe(r As Range) ActiveSheet.Range("E6") = r.Value End Function
The important thing is to separate the cells of the item names and cells of the HYPERLINK formula. Because if you use the same cell's reference, Excel will return a circular reference error. Thus, we keep the formula cells in column B while the names reside in column C.
The next step is to set up the conditional formatting rules. For our scenario a single rule is enough. Our rule checks if the named cell is equal to the target cell of the macro code (C6).
Note: It is important to use relative references in conditional formatting rules. For our scenario, we selected each column to highlight (B9:C9) and used $C6 to ensure that column stays at C while row can be one of the four rows between 6 and 9. The target cell reference is absolute since it's just a single cell ($E$6).