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

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

2. ## Re: conditional formatting fill cells based on text, how to find sum based on CF colour

Welcome to the forums!

Perhaps this?

=SUMIF(C\$2:C\$20,C5,B\$2:B\$20)

You cannot use the colour of a cell in a formula, so you need to use the same logic essentially as the CF rule uses, so the above is saying sum all entries in column B where the corresponding cell in C is the same as the contents of cell C5 (and that could be any cell anywhere on the sheet that contains the name of the company whose figures you need to sum).

3. ## Re: conditional formatting fill cells based on text, how to find sum based on CF colour

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

4. ## Re: conditional formatting fill cells based on text, how to find sum based on CF colour

The paperclip icon doesn't work.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

5. ## Re: conditional formatting fill cells based on text, how to find sum based on CF colour

Hopefully it is attached

6. ## Re: conditional formatting fill cells based on text, how to find sum based on CF colour

What are your expected outcomes and why? I'm not quite sure what you are aiming for here, and you haven't made that clear in your sample sheet or included any of your attempts.

7. ## Re: conditional formatting fill cells based on text, how to find sum based on CF colour

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

8. ## Re: conditional formatting fill cells based on text, how to find sum based on CF colour

I am so sorry - I had not realised that this was a VBA query. Hopefully one of the experts in VBA will be able to help you, but I fear I cannot.

9. ## Re: conditional formatting fill cells based on text, how to find sum based on CF colour

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

10. ## Re: conditional formatting fill cells based on text, how to find sum based on CF colour

Alf - it's available in Office 365 - I'd never noticed it before!

11. ## Re: conditional formatting fill cells based on text, how to find sum based on CF colour

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

## X vBulletin 4.1.8 Debug Information

• Page Generation 0.07668 seconds
• Memory Usage 9,236KB
• Queries Executed 16 (?)
Template Usage (36):
• (2)bbcode_code
• (2)bbcode_quote
• (1)footer
• (1)forumrules
• (1)gobutton
• (11)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (1)postbit_attachment
• (11)postbit_legacy
• (11)postbit_onlinestatus
• (11)postbit_wrapper
• (1)spacer_close
• (1)spacer_open
• (1)tagbit_wrapper

Phrase Groups Available (6):
• global
• inlinemod
• postbit
• posting
• reputationlevel
Included Files (39):
• ./vbseo.php
• ./env.php
• ./vbseo/includes/functions_vbseo.php
• ./vbseo/includes/functions_vbseo_pre.php
• ./vbseo/includes/functions_vbseo_url.php
• ./vbseo/includes/functions_vbseo_createurl.php
• ./vbseo/includes/functions_vbseo_db.php
• ./vbseo/includes/functions_vbseo_vb.php
• ./vbseo/includes/functions_vbseo_seo.php
• ./vbseo/includes/functions_vbseo_misc.php
• ./vbseo/includes/functions_vbseo_crr.php
• ./vbseo/includes/functions_vbseo_cache.php
• ./vbseo/includes/functions_vbseo_hook.php
• ./vbseo/includes/functions_vbseo_startup.php
• ./includes/config.php
• ./global.php
• ./includes/class_bootstrap.php
• ./includes/init.php
• ./includes/class_core.php
• ./includes/functions.php
• ./includes/class_friendly_url.php
• ./includes/class_hook.php
• ./includes/functions_cforum.php
• ./includes/functions_bigthree.php
• ./includes/class_postbit.php
• ./includes/class_bbcode.php
• ./includes/functions_reputation.php
• ./includes/class_bootstrap_framework.php
• ./vb/vb.php
• ./vb/phrase.php
• ./packages/vbattach/attach.php
• ./vb/types.php
• ./vb/cache.php
• ./vb/cache/db.php
• ./vb/cache/observer/db.php
• ./vb/cache/observer.php
• ./includes/functions_notice.php

Hooks Called (49):
• init_startup
• friendlyurl_resolve_class
• database_pre_fetch_array
• database_post_fetch_array
• global_bootstrap_init_start
• global_bootstrap_init_complete
• cache_permissions
• fetch_foruminfo
• global_state_check
• global_bootstrap_complete
• global_start
• style_fetch
• global_setup_complete
• friendlyurl_redirect_canonical
• bbcode_fetch_tags
• bbcode_create
• postbit_factory
• postbit_display_start
• cache_templates
• template_register_var
• parse_templates
• notices_check_start
• notices_noticebit
• process_templates_complete
• reputation_image
• postbit_imicons
• bbcode_parse_start
• bbcode_parse_complete_precache
• bbcode_parse_complete
• postbit_display_complete
• memberaction_dropdown
• postbit_attachment
• tag_fetchbit_complete