Back to Forms in Excel VBA archive index

Back to archive home

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 | ||||

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

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

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

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)

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.

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.

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

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

> 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.

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.