Msg Box with Rich Text

[Pestomania]

I am unsure of how to explain this but I will do my best!

I currently utilize a module that can count and sum based on the color of a cell or the font. It works wonders

https://www.ablebits.com/office-addins-blog/2013/12/12/count-sum-by-color-excel/comment-page-2/

.

Whenever that information is "summed" up on my excel sheet, I want to be able to have a pop-up that when the cell is selected, the msgbox will show all of the parts that were of the same color.

Here is my thoughts:

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Part[/TD]
[TD]Qty[/TD]
[TD]Price[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]4[/TD]
[TD]$5[/TD]
[TD]$20[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD]7[/TD]
[TD]$1[/TD]
[TD]$7[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]20[/TD]
[TD]$0.50[/TD]
[TD]$10[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]5[/TD]
[TD]$1[/TD]
[TD]$5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sum of "Red"[/TD]
[TD][/TD]
[TD][/TD]
[TD]$25[/TD]
[/TR]
</tbody>[/TABLE]


Let's say the "Total" Column is highlighted "red" for "Apple" and "Banana".

Whenever the cell that has $25 in it is selected a pop-up msgbox would have the information such as:

Data to get Sum:
4 Apples at $5 = $20
and
$20 bananas at $0.50 = $10


I hope this all makes sense.

Thank you for any help!

 

[kweaver]

Don't you expect in your example to have a sum of $30?

Why can't you use the SumCellsByFontColor function from that website?

 

[Pestomania]

Please disregard the mistake with the math. It should be $30, not $25.

 

[kweaver]

I suspect you DID use that routine.

I don't know about a popup msgbox, but how about another macro?

Sub ApplyColors()
'   get cell colors from B14 to use to check the data values

    Dim FontColor As Long, BkGrndClr As Long, i As Integer

    With Range("B14")
        FontColor = .Font.Color
        BkGrndClr = .Interior.Color  ' set an interior color to what the data cells should contain.
    End With
'
'   apply colors to the cells
'
    For i = 7 To 12  ' the data was in C7 to C12 -- adjust as needed
      If FontColor = Range("C" & i).Font.Color Then
        Range("C" & i).Interior.Color = BkGrndClr
        Else
      End If
    Next i
End Sub

 

[kweaver]

You might want to add this code after the "End With" -- again, of course, changing your ranges accordingly.

' Clear data cell backgrounds (set to white)
   Range("C7:C12").Interior.ColorIndex = 2

 

[kweaver]

Alternatively, since the grid lines go away with the previous approach, you could do this:

' Clear data cell backgrounds (set to normal)
    Range("C7:C14").Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

 

[Pestomania]

I suspect you DID use that routine.

I don't know about a popup msgbox, but how about another macro?

Sub ApplyColors()
'   get cell colors from B14 to use to check the data values

    Dim FontColor As Long, BkGrndClr As Long, i As Integer

    With Range("B14")
        FontColor = .Font.Color
        BkGrndClr = .Interior.Color  ' set an interior color to what the data cells should contain.
    End With
'
'   apply colors to the cells
'
    For i = 7 To 12  ' the data was in C7 to C12 -- adjust as needed
      If FontColor = Range("C" & i).Font.Color Then
        Range("C" & i).Interior.Color = BkGrndClr
        Else
      End If
    Next i
End Sub

Hi. Thank you for your reply. I don't think understand your macro. The macros I am using work well for what they need to do. Im hoping to be able to select that "total" cell and if would pop up which rows gave me that total.

When I get home, I will try to make an example to post.