Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

sumif function and cell color

Posted by Mike on August 15, 2001 2:51 PM
I would like the sum of a set of numbers based on the color of the cell. I've input conditional formats to color the cell red if it meets certain criteria and now I want to just those cells. I have no VBA knowledge so the easier the better. Thanks

Check out our Excel Resources

Re: sumif function and cell color

Posted by Mark W. on August 15, 2001 3:03 PM
Windows("Quote Package.xls").Activate ActiveWindow.WindowState = xlMaximized ActiveWindow.LargeScroll Down:=1 Range("E50:J50").Select Windows("quotelog.xls").Activate ActiveWindow.WindowState = xlMaximized Range("A5").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False ActiveWorkbook.Save ActiveWindow.Close ActiveWindow.LargeScroll Down:=-1 Range("C22:F22").Select End Sub

I now have a better understanding of your problem (sorry, I thought you wanted the macro to access the file that it resides in, "quotelog.xls" in this case). You could try this (which will assume you have only two files open, "quotelog.xls" and the other file, whatever you name it).

Sub Summary()
Dim FileName1 As String
Dim FileName2 As String

If Application.Windows.Count <> 2 Then
MsgBox prompt:="There can only be 2 files are open", _
Buttons:=vbCritical
Exit Sub
End If
Application.ScreenUpdating = False
Workbooks.Open FileName:="P:\Projects\Jones\Logs\quotelog.xls"
FileName1 = ActiveWorkbook.Name
Rows("5:5").Select
Selection.Copy
Selection.Insert Shift:=xlDown
ActiveWindow.ActivateNext
FileName2 = ActiveWorkbook.Name
Range("E50:J50").Select
Selection.Copy
Windows(FileName1).Activate
Range("A5").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close
Range("C22:F22").Select

End Sub


Let me know if it works for you.

Regards,
Barrie


Re: sumif function and cell color

Posted by Mark W. on August 15, 2001 3:03 PM
Mike, if you assigned colors based on a condition(s)
then why don't you use that condition(s) in a SUMIF
construct? What's your condition(s)? I would like the sum of a set of numbers based on the color of the cell. I've input conditional formats to color the cell red if it meets certain criteria and now I want to just those cells. I have no VBA knowledge so the easier the better. Thanks


Re: sumif function and cell color

Posted by Mike on August 15, 2001 4:41 PM
My conditions are that if the cell is one of a list of numbers i.e. 1,2,3,or 4 then the cell be red based on the conditional format. How do input tat same condition into a sumif function. I thought sumif functions could only be based on one condition.

Mike, if you assigned colors based on a condition(s)


Re: sumif function and cell color

Posted by Mark W. on August 15, 2001 4:52 PM
You're right about the limitations of SUMIF(), but
keep in mind that this formula was added to Excel
so that oft-misunderstood array formulas could be
avoided (at least for simple cases). In your
case the array formula, {=SUM((A1:A5={1,2,3,4})+0)},
will suffice! If A1:A5 contains {1;3;6;7;4} this
formula will produce a count of 3. Remember,
array formulas must be enter using the Ctrl+Shift+Enter
key combination, and the outermost braces, {}, are
not entered by you -- they're supplied by Excel
in recognition of a properly entered array formula. My conditions are that if the cell is one of a list of numbers i.e. 1,2,3,or 4 then the cell be red based on the conditional format. How do input tat same condition into a sumif function. I thought sumif functions could only be based on one condition.


Re: sumif function and cell color

Posted by Mike on August 15, 2001 5:21 PM
I appreciate your help up to this point but maybe i didn't mke my self clear on one point. I need the sum of the highlighted cells instead of the Count. Your formula counts the number of cells that meet the criteria but I need to get the sum total. Sorry for the confusion.

You're right about the limitations of SUMIF(), but


Re: sumif function and cell color

Posted by Aladin Akyurek on August 15, 2001 11:41 PM

{=SUM((A1:A5={1,2,3,4})*(A1:A5)}


Re: sumif function and cell color

Posted by Mark W. on August 16, 2001 7:18 AM
> Sorry for the confusion.

The confusion was on my part. All you need to
do is make a slight modification to the array
formula...

{=SUM((A1:A5={1,2,3,4})*A1:A5)} I appreciate your help up to this point but maybe i didn't mke my self clear on one point. I need the sum of the highlighted cells instead of the Count. Your formula counts the number of cells that meet the criteria but I need to get the sum total. Sorry for the confusion.


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.