conditional formatting fill cells based on text, how to find sum based on CF colour

Hi Guys
my name is Anthony.
i am new to excel and spread sheets, so please bear with me.

i have a spread sheet were i have used conditional formatting to highlight cells (and the next cells to the left) that contain a certain text.
for example every time "Bunnings, Wattyl, Home hardware" appears in column C, it is colour filled blue along with the next cell to the left.
so if bunnings appears in C5, then cells C5 and B5 are colour filled blue.
coloum B contains $ amounts


i have used the following formula

=SUM(IF(ISERROR((SEARCH('C:\TAX\[Tax template 2.xlsm]Key'!#REF!,B1:C1))),0,1))

i have used a key (spread sheet) to determine which words to search for.

what i want to know is if i Want to find the Sum of the blue filled cells in column B ($ amounts) how would i do it.

as i have used conditional formatting i can not use "SumCellsByColor" as they will not recognize the formatted colour fill.

any ideas

Thanks

Hi Ali
thanks for the quick response.
as i am searching for many different texts not just one, this formula wont work. i have attached a spread sheet with the basics. sorry the last formula was not completely correct i have been experimenting a bit and have saved a number of spread sheets.
hopefully this will make more sense.

how do i Attach a spread sheet. sorry my bad i cant seem to get the attachment icon to do anything

Hopefully it is attached

so basically i have go around 50-60 re occurring items per month that i need to separate into 10 different groups and find the sum of each group.
each of the 10 groups is represented by a certain colour
or example anything to do with building materials will be highlighted blue.
anything to do with vehicles will be highlighted green.
anything to do with personnel will be highlighted brown.

and so on

there are also items that are not reoccuring that may come up once a year or so, but ill deal with that later.

in the past i would colour fill each individual item (looking at 100-200 items in a month) according to what group they should be in.

and then use the following
macro to find the sum of the colour filled item according to that colour.

Function GetCellColor(xlRange As Range)
Dim indRow, indColumn As Long
Dim arResults()

Application.Volatile

If xlRange Is Nothing Then
Set xlRange = Application.ThisCell
End If

If xlRange.Count > 1 Then
ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)
For indRow = 1 To xlRange.Rows.Count
For indColumn = 1 To xlRange.Columns.Count
arResults(indRow, indColumn) = xlRange(indRow, indColumn).Interior.Color
Next
Next
GetCellColor = arResults
Else
GetCellColor = xlRange.Interior.Color
End If
End Function

Function GetCellFontColor(xlRange As Range)
Dim indRow, indColumn As Long
Dim arResults()

Application.Volatile

If xlRange Is Nothing Then
Set xlRange = Application.ThisCell
End If

If xlRange.Count > 1 Then
ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)
For indRow = 1 To xlRange.Rows.Count
For indColumn = 1 To xlRange.Columns.Count
arResults(indRow, indColumn) = xlRange(indRow, indColumn).Font.Color
Next
Next
GetCellFontColor = arResults
Else
GetCellFontColor = xlRange.Font.Color
End If

End Function

Function CountCellsByColor(rData As Range, cellRefColor As Range) As Long
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long

Application.Volatile
cntRes = 0
indRefColor = cellRefColor.Cells(1, 1).Interior.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Interior.Color Then
cntRes = cntRes + 1
End If
Next cellCurrent

CountCellsByColor = cntRes
End Function

Function SumCellsByColor(rData As Range, cellRefColor As Range)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes

Application.Volatile
sumRes = 0
indRefColor = cellRefColor.Cells(1, 1).Interior.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Interior.Color Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
End If
Next cellCurrent

SumCellsByColor = sumRes
End Function

Function CountCellsByFontColor(rData As Range, cellRefColor As Range) As Long
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long

Application.Volatile
cntRes = 0
indRefColor = cellRefColor.Cells(1, 1).Font.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Font.Color Then
cntRes = cntRes + 1
End If
Next cellCurrent

CountCellsByFontColor = cntRes
End Function

Function SumCellsByFontColor(rData As Range, cellRefColor As Range)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes

Application.Volatile
sumRes = 0
indRefColor = cellRefColor.Cells(1, 1).Font.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Font.Color Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
End If
Next cellCurrent

SumCellsByFontColor = sumRes
End Function

along with
SumCellsByColor('Bank Account'!$B$13:$B$25,A8)
to find the sum of each colour group
the A8 being a certain colour filled cell say red


i am now using Format conditioning to recognize certain text in these reoccuring items and highlight them for me.
As a result the above macro wont recognize the colour fill done by the conditional formatting.

so what i am after is a Macro that will recognize the cells filled in by the format conditioning and allow me to find the sum of each colour group.


i hope this has made things clearer

cheers Anthony

In Excel 2010 you have the possibility to filter a column by color, don't know if this is possible Office 360 pro.

The way to set this up in Excel 2010 is first you add a formula in say B8

Please Login or Register  to view this content.

The 9 is to get the sum value. Then you select range A1:C1 and click "Filter" (Data tab), then click on the "Drop down" arrow in B1 and select "Filter by color" and click on the color shown.


color_filter.jpg


Alf

I'd never noticed it before!

Well I think we all get surprised from time to time when we learn what is possible in Excel.

And if the OP wish for a macro to do this then it could look like this:

Please Login or Register  to view this content.

and just add the "SUBTOTAL" formula at the appropriate row or set it with the macro.

Alf