Change color of cell when moving mouse over it

Delton

Board Regular
Joined
Oct 19, 2002
Messages
66
Hello,
The Excell Board "Topic" column changes color as you hover over it without selecting it. Is this an Excel/VB code? I have read about similar events here before, but the last one I tried required actually selecting a cell. If this has been previously discussed, does anyone recall the topic name. Thanks, Delton.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Impossible and cannot be done. Excel does not support that functionality over naked cells.

It sounds like you want to just glide or position your mouse over cell(s) and have the interior color of the cell be changed, without selecting (clicking on) any cell(s). That is not possible to accomplish with naked cells. The closest you can come to that is a MouseMove event over a transparent command button, label, or some other ActiveX control set on top of a cell or range, whose properties, unlike spreadsheet cells, respond to MouseMove. That would have it's drawbacks too, among them is the increased difficulty in selecting the cell(s) with a mouse when a transparent control is placed over it like a plexiglass shield.
 
Upvote 0
Hi,

Below are two procedures that shoul be added to 2 Buttons. On e for starting this functionality to cell A1 and the other button for stopping it.

Note that this slows downn the system:


<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Function</SPAN> GetCursorPos <SPAN style="color:#00007F">Lib</SPAN> "user32" (lpPoint <SPAN style="color:#00007F">As</SPAN> POINTAPI) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Type</SPAN> POINTAPI
x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Type</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> lngCurPos <SPAN style="color:#00007F">As</SPAN> POINTAPI
<SPAN style="color:#00007F">Dim</SPAN> Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>


<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> ChangeCellColor()

Canel = <SPAN style="color:#00007F">False</SPAN>
lngA1Left = ActiveWindow.PointsToScreenPixelsX(Range("a1").Left)
lngA1Right = ActiveWindow.PointsToScreenPixelsX(Range("a1").Offset(0, 1).Left)
lngA1Top = ActiveWindow.PointsToScreenPixelsY(Range("a1").Top)
lngA1Bottom = ActiveWindow.PointsToScreenPixelsY(Range("a1").Offset(1, 0).Top)

    <SPAN style="color:#00007F">Do</SPAN>
        GetCursorPos lngCurPos
        <SPAN style="color:#00007F">If</SPAN> lngCurPos.x > lngA1Left And lngCurPos.x < lngA1Right <SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#00007F">If</SPAN> lngCurPos.y > lngA1Top And lngCurPos.y < lngA1Bottom <SPAN style="color:#00007F">Then</SPAN>
                Range("a1").Interior.Color = vbRed
            <SPAN style="color:#00007F">Else</SPAN>
                Range("a1").Interior.ColorIndex = 0
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        DoEvents
    <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">Until</SPAN> Cancel = <SPAN style="color:#00007F">True</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CancelProcedure()
    Cancel = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>


Hope this helps.
 
Upvote 0
Thank you all for the clarification. It was a question of curiousity more than need, but I was compelled to ask anyway. It always nice to have new toys. I will try the rafaaj2000 post to see what it will offer then.
 
Upvote 0
Delton said:
I will try the rafaaj2000 post to see what it will offer then.
This adds the functionallity to A1 only...but will work as desired for that cell.
 
Upvote 0
Son of a gun, Jaafar, I'd seen this question here before and had seen responses like Tom's (I think verbatim); so I was very surprised to see it done! Kudos! :bow:

FWIW - I tried to get this to work with a larger set of cells. While it appears to "sort of work" the results are "spotty" at best - at least with my CPU...

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Function</SPAN> GetCursorPos <SPAN style="color:#00007F">Lib</SPAN> "user32" (lpPoint <SPAN style="color:#00007F">As</SPAN> POINTAPI) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Type</SPAN> POINTAPI
    x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Type</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Type</SPAN> Coordinates
    lngLeft <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    lngRight <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    lngTop <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    lngBottom <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    rngCell <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Type</SPAN>


<SPAN style="color:#00007F">Dim</SPAN> lngCurPos <SPAN style="color:#00007F">As</SPAN> POINTAPI
<SPAN style="color:#00007F">Dim</SPAN> Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>


<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> ChangeCellColor()
    <SPAN style="color:#00007F">Dim</SPAN> r <SPAN style="color:#00007F">As</SPAN> Range, coorCells() <SPAN style="color:#00007F">As</SPAN> Coordinates, l <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, c <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Set</SPAN> r = Selection
    <SPAN style="color:#00007F">ReDim</SPAN> coorCells(r.Count)
    
    Cancel = <SPAN style="color:#00007F">False</SPAN>

    <SPAN style="color:#00007F">For</SPAN> l = 1 <SPAN style="color:#00007F">To</SPAN> r.Count
        <SPAN style="color:#00007F">Set</SPAN> c = r.Cells(l)
        <SPAN style="color:#00007F">With</SPAN> ActiveWindow
            coorCells(l).lngLeft = .PointsToScreenPixelsX(c.Left)
            coorCells(l).lngRight = .PointsToScreenPixelsX(c.Offset(, 1).Left)
            coorCells(l).lngTop = .PointsToScreenPixelsY(c.<SPAN style="color:#00007F">To</SPAN>p)
            coorCells(l).lngBottom = .PointsToScreenPixelsY(c.Offset(1).Top)
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        <SPAN style="color:#00007F">Set</SPAN> coorCells(l).rngCell = c
    <SPAN style="color:#00007F">Next</SPAN> l
    r.Interior.ColorIndex = 24

    <SPAN style="color:#00007F">Do</SPAN>
        <SPAN style="color:#00007F">For</SPAN> l = 1 To r.Count
            GetCursorPos lngCurPos
            <SPAN style="color:#00007F">If</SPAN> lngCurPos.x > coorCells(l).lngLeft And lngCurPos.x < coorCells(l).lngRight <SPAN style="color:#00007F">Then</SPAN>
                <SPAN style="color:#00007F">If</SPAN> lngCurPos.y > coorCells(l).lngTop And lngCurPos.y < coorCells(l).lngBottom <SPAN style="color:#00007F">Then</SPAN>
                    coorCells(l).rngCell.Interior.Color = vbYellow
                <SPAN style="color:#00007F">Else</SPAN>
                    coorCells(l).rngCell.Interior.ColorIndex = 0
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
            DoEvents
        <SPAN style="color:#00007F">Next</SPAN> l
    <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">Until</SPAN> Cancel = <SPAN style="color:#00007F">True</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CancelProcedure()
    Cancel = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>
 
Upvote 0
Jaafar - -

I admire your persistence with this topic, on this and other threads. I am not trying to deflate yours or anyone's enthusiasm in finding a solution, but I still say what you want to do cannot be done. I hope I am wrong, and if anyone, including me, comes up with code to do this, I'll send you a 12-pack of locally brewed Anchor Steam Beer and a couple loaves of genuine sourdough bread, fresh from the markets at Fisherman's Wharf here in San Francisco where I live.

Even the code you posted does not really work for cell A1, nor does it get triggered by cell A1 being the cell under which the mouse is hovering. I know cell A1 gets shaded to give the illusion that the code acts on cell A1, but really it does not. Consider two items:

(1)
What your code does is play off cell A1 because that cell happens to be in the upper left corner of the worksheet on most systems. The POINTAPI structure describes a location with X and Y coordinates as you have used. Left and Top fields describe the corner of the cell (a rectangular object), and because cell A1 is usually in the upper left corner of a worksheet, it is uniquely positioned to have its Left and Top POINTAPI fields reliably identified on most systems.

However, you can test for yourself why cell A1 really is not the trigger itself. Go to Tools > Options > International tab, and select "View current sheet right-to-left". That will place A1 where usually cell IV1 is, and reverse the order of the columns. Now try your code by hovering your mouse over cell A1. Unfortunately your code will fail because now the top and left fields mean nothing for cell A1, as its top left corner now abuts the top right corner and edge of a cell (B1), not the edge of the active window.

This brings us to my second point:

(2)
With the international right-to-left option still in place, try the code again by revising it for cell IV1 instead of A1. Unfortunately, it will fail again, this time because even though cell IV1 is in the upper left corner or the screen, its upper left corner (and left edge) is still manipulate-able...it is not rigid like cell A1's left edge is. Test this on your current standard worksheet by placing your mouse on the left edge of the column A header. You do not get the double-ended arrow to allow for expansion of column A left-ward, as you do to expand any column (*including IV*) left-ward and right-ward.

The crux of this second point is, the POINTAPI structure does not have a Right or Bottom field to accompany its Top and Left fields. Its counterpart RECT function does have those two additional fields, but they speak to the object's size, not its location. Unless you know where one cell ends (location), not just how big it is (size), the act of a mouse hovering over a cell does not allow for determining either field when all other borders (other cells) are unknown.

That means, unfortunately, your code does not treat A1 because it is A1, but because it is in the top left corner in a standard left-to-right environment. That further means your code is not triggered by your mouse hovering over any cell, which brings us back to my belief that naked cells do not and cannot trigger a mouse move event, upon which any formatting or manipulation of such cells can take place.

Now, maybe some applicability can be had by a user who coincidentally, but not likely, has cell A1 and only cell A1 in mind for any such format change, but outside of that, I think the other 16,777,215 worksheet cells are out of luck.

My offer still stands for SF beer and sourdough if we can find a solution, and I'll poke around, but it seems like a bleak prospect. Let me know if you find anything that does work, and certainly I will do the same. Good luck to us all with this one !!
 
Upvote 0
Tom ,

Thanks for the detailed and enlightning explanation.I agree with you in that this is by no means an accurate solution and as I mentioned in a previous post on a similar topic a couple of days ago, I am aware the code doesn't work accuratly for other cells.However I find subjects like these so exciting and educational that I am constantly trying new methods.

Looking forward to sampling the Brew :hungry: :diablo:

Regards.
 
Upvote 0
Ahh....Tom.... since it was my question this time around.... am I elligible for one of those beers too? Kind of like a "class action" thing for being caught up in the collateral frenzy?
 
Upvote 0

Forum statistics

Threads
1,223,884
Messages
6,175,175
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top