Font colors sensitive SUMIF

SG4

New Member
Joined
Apr 19, 2012
Messages
6
Hello!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
Can somebody help me out with some VBA solution for my below problem:<o:p></o:p>
-I have two columns<o:p></o:p>
-In column »A« are listed values in black and red font color<o:p></o:p>
-I need a SUM of values inside of column »B« where in the same row in column »A« value is written in red color.<o:p></o:p>
Example:<o:p></o:p>
<o:p><TABLE style="WIDTH: 190pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=252><COLGROUP><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" span=2 width=64><COL style="WIDTH: 47pt" span=2 width=62><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl78 height=16 width=64>A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl78 width=64>B</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74 width=62></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74 width=62></TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 height=15>15,000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl77>60,0000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75>Result:</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl81 height=15>35,000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl77>70,0000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl82 align=right>68,0000</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl76 height=15>25,0000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl77>50,0000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75></TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl79 height=15>2,0000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl77>8,0000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75></TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl76 height=15>6,0000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl77>18,0000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75></TD></TR></TBODY></TABLE></o:p>
How to make this automatically at larger tables?<o:p></o:p>
Thanks.<o:p></o:p>
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Maybe:

Code:
Sub SG4()
Dim lr As Long
Dim x As String
Dim rcell As Range

x = InputBox("Please Enter the Font Color Index")

lr = Cells(Rows.Count, 1).End(xlUp).Row

For Each rcell In Range("A2:A" & lr)

    If rcell.Font.ColorIndex = x Then
    
        Range("D1").Value = Range("D1").Value + rcell(, 2).Value
        
    End If
    
Next rcell

End Sub

This assumes that the result goes in D1
 
Last edited:
Upvote 0
Hi,

I copy this in to a VBA module and change "X" in to color index, but somehow this not work in Excel.
Any idea what went wrong on my side?
Thanks!
 
Upvote 0
I mean this VBA function should allow me to:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p> </o:p>
-free selection of rows inside of one column where different values in black, red,….colors are listed<o:p></o:p>
-free selection of color index<o:p></o:p>
-selection of rows inside of one column where are actual values that needs to be add if in first selected column value is in selected color index<o:p></o:p>
<o:p> </o:p>
<o:p>Many thanks for your help in advance</o:p>
 
Upvote 0
Hi,

I copy this in to a VBA module and change "X" in to color index, but somehow this not work in Excel.
Any idea what went wrong on my side?
Thanks!


An Inputbox should pop up requesting a color index. Based on your sample I used 1 for black and 3 for red. When I tested it worked, if I understood correctly.
 
Upvote 0
Hi!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p> </o:p>
I copy this in to a new VBA module, but this function I could not find later in Excel……<o:p></o:p>
 
Upvote 0
Hi!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p> </o:p>
I copy this in to a new VBA module, but this function I could not find later in Excel……<o:p></o:p>

I have 2003. For 2003 and earlier Menus Tools>Macro>Macros select the name and hit run.
 
Upvote 0
Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p> </o:p>
It is possible to change this in to a VBA function which will allow me to:
<o:p> </o:p>
-free selection of rows inside of one column where different values in black, red,….colors are listed
-free selection of color index
-selection of rows inside of one column where are actual values, that needs to be add if in first selected column value is in selected color index

Many thanks for your help in advance.<o:p></o:p>
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,874
Members
452,363
Latest member
merico17

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