Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Highlighted Cells
I have highlighted cells within a spreadsheet by changing the box color to
yellow. Is there anyway to easily create a formula to SUM these cells for me? Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Highlighted Cells
This is code from Chip Pearson's site which sums cells with a given backgound
colour or font. Function SumByColor(InRange As Range, WhatColorIndex As Integer, _ Optional OfText As Boolean = False) As Double ' ' This function return the SUM of the values of cells in ' InRange with a background color, or if OfText is True a ' font color, equal to WhatColorIndex. ' Dim Rng As Range Dim OK As Boolean Application.Volatile True For Each Rng In InRange.Cells If OfText = True Then OK = (Rng.Font.ColorIndex = WhatColorIndex) Else OK = (Rng.Interior.ColorIndex = WhatColorIndex) End If If OK And IsNumeric(Rng.Value) Then SumByColor = SumByColor + Rng.Value End If Next Rng End Function You can call this function from a worksheet cell with a formula like =SUMBYCOLOR(A1:A10,3,FALSE) <==Background colour 3 =SUMBYCOLOR(A1:A10,3,TRUE) <== Font colour 3 HTH "Jenny" wrote: I have highlighted cells within a spreadsheet by changing the box color to yellow. Is there anyway to easily create a formula to SUM these cells for me? Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Highlighted Cells
Here are 2 custom formulas that will work for you. The first one uses a
named range called "MyData" the other formula does exactly the same thing as the first, only you provide the cell address when you enter the formula into the spreadsheet. CalcYellowDogCellRedux(A1:X100) interior color index of 6 is yellow.. ---------------------------------------------------------------------- Function CalcYellowDogCells() As Double Dim wb As Workbook Dim ws As Worksheet Dim r As Range Dim dblSum As Double Dim l As Long Application.Volatile Set wb = ActiveWorkbook Set ws = wb.Worksheets("Sheet1") ws.Activate Set r = Range("MyData") For l = 1 To r.Cells.Count If r.Cells(l).Interior.ColorIndex = 6 Then dblSum = dblSum + r.Cells(l).Value End If Next l Set wb = Nothing Set ws = Nothing Set r = Nothing CalcYellowDogCells = dblSum Exit Function End Function ---------------------------------------------------------------------- Function CalcYellowDogCellsRedux(CellAddresses As Range) As Double Dim wb As Workbook Dim ws As Worksheet Dim r As Range Dim dblSum As Double Dim l As Long Application.Volatile Set wb = ActiveWorkbook Set ws = wb.Worksheets("Sheet1") ws.Activate Set r = CellAddresses For l = 1 To r.Cells.Count If r.Cells(l).Interior.ColorIndex = 6 Then dblSum = dblSum + r.Cells(l).Value End If Next l Set wb = Nothing Set ws = Nothing Set r = Nothing CalcYellowDogCellsRedux = dblSum Exit Function End Function ---------------------------------------------------------------------- -- Kevin Backmann "Jenny" wrote: I have highlighted cells within a spreadsheet by changing the box color to yellow. Is there anyway to easily create a formula to SUM these cells for me? Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Highlighted Cells
See http://www.xldynamic.com/source/xld.ColourCounter.html for a working
solution -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Jenny" wrote in message ... I have highlighted cells within a spreadsheet by changing the box color to yellow. Is there anyway to easily create a formula to SUM these cells for me? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I protect only the highlighted cells in Excel? | Excel Discussion (Misc queries) | |||
How do I count cells that have are highlighted a certain color? | Excel Worksheet Functions | |||
Can I sum only highlighted cells in a worksheet? | Excel Worksheet Functions | |||
Help adding text values | Excel Worksheet Functions | |||
Convert data type of cells to Text,Number,Date and Time | Excel Worksheet Functions |