Does VBA Exist In Excel That Allows One To Hover Over A Certain Colored Cell and Make A Small Pop Up Text Box Appear With A Value On Another A Hidden

[Aimee S.]

Hi, Excel Genius Type Persons!

This is perhaps pie-in-the-sky stuff, but over break a thought occurred to me that would be ever-so-amazing if it were possible, so I figured it would not hurt to ask!!!

Let's say I have conditional formatting in my excel file that fills cells with orange and changes the text to blue. The exact code for that exact color scheme is:

.Interior.Color = RGB(255, 211, 167).Font.Color = RGB(0, 51, 153)

Would it be possible to have an additional set of VBA instructions in place, say, that if any cell has this precise formatting in the workbook, one can hover their mouse over that conditionally-formatted cell and a small text box appear that shows what a cell value is on a hidden sheet, such that that small text box disappears when you are not hovering your mouse over that colored cell?

The additional rule applied would dictate that if the conditionally-formatted orange cell is on any given worksheet, the pop-up text box upon hovering over that cell would pull the value that exists in that same cell (row and column #) on the hidden, corresponding worksheet (for example, I have worksheets called "AL", "AZ", "CA" and so forth for state abbreviations on which the conditional formatting appears, and the corresponding worksheets from which the pop-up text box would pull would always be called "Prior AL", "Prior AZ", or "Prior CA" etc.

This would allow a user to see A) that the value in say, cell A10 on the "AL" worksheet has changed since the last iteration was run, via the already-in-place conditional formatting rule, but hopefully (if possible) B) by hovering over cell A10 on the "AL" worksheet the value in cell A10 on the "Prior AL" worksheet would appear so long as they remained hovered over the A10 cell on the "AL" worksheet.

Impossible? Thanks in advance for humoring my rambling description, and for your valuable time!

Best,
Aimee

 

[Eric W]

What you're describing sounds very much like a cell comment. What you could do is create a Worksheet_Change macro. When something on your sheet changes, it would kick off. It would check the range of your Conditional Formatting, and using the same rule that CF uses, if it finds a matching cell, it would create a comment for that cell with the data you want. Non-matching cells would have the comments removed. If this sounds feasible, let me know, as well as what your CF rule(s) is.

 

[Aimee S.]

Good Afternoon, Eric!

Thank you for your response. Below is the code that resets the conditional formatting and is "CALL"ed each week when a set of macros are run to insert fresh data into each sheet. How would I go about utilizing the code below as you state, or enhance the code such that the 1st conditional formatting rule does what you are suggesting? Please know I am a novice at best with coding and while I can do very basic things I have generally had some assistance in the past with more complex cell comparison conditional formatting rule programming like what you see below! All Best - Aimee

Option Explicit

Sub NEW_Reset_Formatting()
    Dim ws As Variant
    Dim LR As Long
    ' ----------------------------------------------------------------------------------------
    For Each ws In Array("AL", "AZ", "CA", "CO", "FL", "GA", "ID", "IN", "KY", "ME", "MI", "MN", "MS", "NH", "NY", "OH", "OK", "PA", "SC", "TN", "VA", "VT", "WA", "WI", "XX")
        Set ws = Sheets(ws)
        With ws
            LR = .Range("G" & .Rows.Count).End(xlUp).Row + 1
            On Error GoTo ErrHandler
            ' Disable Events:
            Application.EnableEvents = False
            ' Delete All Conditional Formatting Rules In Sheet:
            .Cells.FormatConditions.Delete
            ' Recreate All The Conditional Formatting Rules:
            [COLOR=#FF0000]With .Cells(1, 1).FormatConditions.Add(Type:=xlExpression, Formula1:= _
                                                   "=A1<>'Prior " & ws.Name & "'!A1")
                .Interior.Color = RGB(255, 211, 167)
                .Font.Color = RGB(0, 51, 153)
            End With[/COLOR]
            With .Cells(1, 1).FormatConditions.Add(Type:=xlExpression, Formula1:= _
                                                   "=COUNTIF(1:1,""*Total*"")")
                .Interior.Color = RGB(220, 230, 241)
                .Font.Color = RGB(31, 73, 125)
                .Font.FontStyle = "Bold Italic"
                .SetFirstPriority
            End With
            With .Cells(1, 1).FormatConditions.Add(Type:=xlCellValue, Operator:=xlEqual, _
                                                   Formula1:="=""Green""")
                .Interior.Color = RGB(0, 255, 0)
                .Font.Color = RGB(0, 0, 0)
            End With
            With .Cells(1, 1).FormatConditions.Add(Type:=xlCellValue, Operator:=xlEqual, _
                                                   Formula1:="=""Yellow""")
                .Interior.Color = RGB(255, 255, 0)
                .Font.Color = RGB(0, 0, 0)
            End With
            With .Cells(1, 1).FormatConditions.Add(Type:=xlCellValue, Operator:=xlEqual, _
                                                   Formula1:="=""Red""")
                .Interior.Color = RGB(255, 0, 0)
                .Font.Color = RGB(0, 0, 0)
            End With
            ' Modify the "Applies To" Ranges:
            .Cells.FormatConditions(1).ModifyAppliesToRange Range("A4:BD500")
            .Cells.FormatConditions(2).ModifyAppliesToRange Range("A4:BD500")
            .Cells.FormatConditions(3).ModifyAppliesToRange Range("G4:G500")
            .Cells.FormatConditions(4).ModifyAppliesToRange Range("G4:G500")
            .Cells.FormatConditions(5).ModifyAppliesToRange Range("G4:G500")
        End With
    Next ws
ErrHandler:
    Application.EnableEvents = True
    Worksheets("Control Panel").Activate
    Range("A1").Select
End Sub

What you're describing sounds very much like a cell comment. What you could do is create a Worksheet_Change macro. When something on your sheet changes, it would kick off. It would check the range of your Conditional Formatting, and using the same rule that CF uses, if it finds a matching cell, it would create a comment for that cell with the data you want. Non-matching cells would have the comments removed. If this sounds feasible, let me know, as well as what your CF rule(s) is.

 

[Aimee S.]

Oh and one additional ask: if what you suggest is possible, could the pop-up text box over orange cells like you talked about read "Was: " and then the value? So say the value in the text box that appears is $5,467, the pop-up text box would say "Was $5,467".

Also, if it helps, there are three types of values that would apply here... some dates, some $s, and some text strings. Essentially this is a massive Program / Construction project Management tool utilizing BI Data inserted each week into a data dump tab and the file calculates and presents the data in dashboard fashion by state.

 

[Eric W]

You can probably just modify that code a bit to get what you want, like this:

Option Explicit


Sub NEW_Reset_Formatting()
    Dim w As Variant
    Dim LR As Long
    Dim c As Variant
    Dim c2 As Range
    Dim Priorws As Worksheet
    ' ----------------------------------------------------------------------------------------
    For Each w In Array("AL", "AZ", "CA", "CO", "FL", "GA", "ID", "IN", "KY", "ME", "MI", "MN", "MS", "NH", "NY", "OH", "OK", "PA", "SC", "TN", "VA", "VT", "WA", "WI", "XX")
        Set Priorws = Sheets("Prior " & w)
        With Sheets(w)
            LR = .Range("G" & .Rows.Count).End(xlUp).Row + 1
            On Error GoTo ErrHandler
            ' Disable Events:
            Application.EnableEvents = False
            ' Delete All Conditional Formatting Rules In Sheet:
            .Cells.FormatConditions.Delete
            ' Recreate All The Conditional Formatting Rules:
            With .Cells(1, 1).FormatConditions.Add(Type:=xlExpression, Formula1:= _
                                                   "=A1<>'Prior " & .Name & "'!A1")
                .Interior.Color = RGB(255, 211, 167)
                .Font.Color = RGB(0, 51, 153)
            End With
            With .Cells(1, 1).FormatConditions.Add(Type:=xlExpression, Formula1:= _
                                                   "=COUNTIF(1:1,""*Total*"")")
                .Interior.Color = RGB(220, 230, 241)
                .Font.Color = RGB(31, 73, 125)
                .Font.FontStyle = "Bold Italic"
                .SetFirstPriority
            End With
            With .Cells(1, 1).FormatConditions.Add(Type:=xlCellValue, Operator:=xlEqual, _
                                                   Formula1:="=""Green""")
                .Interior.Color = RGB(0, 255, 0)
                .Font.Color = RGB(0, 0, 0)
            End With
            With .Cells(1, 1).FormatConditions.Add(Type:=xlCellValue, Operator:=xlEqual, _
                                                   Formula1:="=""Yellow""")
                .Interior.Color = RGB(255, 255, 0)
                .Font.Color = RGB(0, 0, 0)
            End With
            With .Cells(1, 1).FormatConditions.Add(Type:=xlCellValue, Operator:=xlEqual, _
                                                   Formula1:="=""Red""")
                .Interior.Color = RGB(255, 0, 0)
                .Font.Color = RGB(0, 0, 0)
            End With
            ' Modify the "Applies To" Ranges:
            .Cells.FormatConditions(1).ModifyAppliesToRange Range("A4:BD500")
            .Cells.FormatConditions(2).ModifyAppliesToRange Range("A4:BD500")
            .Cells.FormatConditions(3).ModifyAppliesToRange Range("G4:G500")
            .Cells.FormatConditions(4).ModifyAppliesToRange Range("G4:G500")
            .Cells.FormatConditions(5).ModifyAppliesToRange Range("G4:G500")
            
            .Range("A4:BD500").ClearComments
            For Each c In .Range("A4:BD500")
                Set c2 = Priorws.Range(c.Address)
                If c.Value <> c2.Value Then
                    c.AddComment
                    c.Comment.Text Text:="Was: " & WorksheetFunction.Text(c2.Value, c.NumberFormat)
                End If
            Next c


        End With
    Next w
    
ErrHandler:
    Application.EnableEvents = True
    Worksheets("Control Panel").Activate
    Range("A1").Select
End Sub

I made a few tweaks here and there, but mostly the code I added is in red. Let me know how this works.

 

[Aimee S.]

You did it. You actually did it. I was just about to leave the office for the weekend, and I was so excited to see an email saying you'd replied just to see if this was possible and put off leaving the office just to see if this worked, and it does. Beautifully.

Outstanding assistance, friend. I can't thank you enough!! This feature of being able to see what the value was the previous week as opposed to just seeing the cell highlighted (indicating the value changed with no quantification) is a game changer for our group.

Have an amazing weekend and you, sir, win the excel forum internetwebs today! :D :D :D

Warm Regards,
Aimee in Madison

 

[Eric W]

Happy to help! Enjoy your weekend!

 

[Aimee S.]

Good Morning, Eric! I am wondering if you think it is possible to combine the following minor aesthetic tweaks to the code you generously offered Friday: two final touches would be to alter the font for all comment boxes in the workbook (likely utilizing the same named worksheet array formula that you altered above) to Khmer UI, size 10, keep color as black, and auto-fit all comment boxes so that the comment boxes are only as big as the text that pops up (e.g. there is a lot of empty space if the comment only says "Was: 0". I found a couple of examples below of code that does this but am having trouble combining what they do with the existing Reset_Formatting VBA I shared with you Friday (I am trying to keep the number of macros to a minimum). If you happen to have a free moment to help I would be very grateful. Thanks again for your help on Friday!! Very excited!

Sub Fit_Range_Comments()Dim WorkRng As Range
Set WorkRng = Application.Selection
For Each rng In WorkRng
    If Not rng.Comment Is Nothing Then
        rng.Comment.Shape.TextFrame.AutoSize = True
    End If
Next
End Sub

Sub Set_Comments_Properties()Dim Cell As Range
For Each Cell In Selection
If Not Cell.Comment Is Nothing Then
With Cell.Comment.Shape.TextFrame.Characters.Font
.ColorIndex = 1
.Size = 10
.Name = "Khmer UI"
End With
End If
Next Cell
End Sub

The goal is to get these two code ideas into the NEW_Reset_Formatting() VBA string shared on Friday.

 

[Eric W]

Good morning, Aimee!

I hope you had a nice weekend. You're very close to fixing the format of the comments yourself, it's just a matter of putting things in the right order. Put this code in place of the red block from the code in post #5 :

            .Range("A4:BD500").ClearComments
            For Each c In .Range("A4:BD500")
                Set c2 = Priorws.Range(c.Address)
                If c.Value <> c2.Value Then
                    With c.AddComment
                        .Text Text:="Was: " & WorksheetFunction.Text(c2.Value, c.NumberFormat)
                        .Shape.TextFrame.Characters.Font.Size = 10
                        .Shape.TextFrame.Characters.Font.Name = "Khmer UI"
                        .Shape.TextFrame.AutoSize = True
                    End With
                End If
            Next c

Let me know how this works.

 

[Aimee S.]

Works perfectly!!! That you very much, Eric. I love how simple / elegant this solution is. Less individual macros wherever possible = Better workbook. I hope you had a good weekend too. Game of Thrones was the highlight, of course. If you'd like to see an intentionally modified-for-public-viewing screenshot of what your assistance resulted in feel free to have a peek: Imgur: The most awesome images on the Internet