+ Reply to Thread
Results 1 to 11 of 11

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

  1. #1
    Registered User
    Join Date
    08-27-2016
    Location
    AUSTRALIA
    MS-Off Ver
    360 pro
    Posts
    4

    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. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,076

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


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Registered User
    Join Date
    08-27-2016
    Location
    AUSTRALIA
    MS-Off Ver
    360 pro
    Posts
    4

    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. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,076

    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. #5
    Registered User
    Join Date
    08-27-2016
    Location
    AUSTRALIA
    MS-Off Ver
    360 pro
    Posts
    4

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

    Hopefully it is attached
    Attached Files Attached Files

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,076

    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. #7
    Registered User
    Join Date
    08-27-2016
    Location
    AUSTRALIA
    MS-Off Ver
    360 pro
    Posts
    4

    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. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,076

    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. #9
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,671

    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. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,076

    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. #11
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,671

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Help doing conditional formatting based on size cell to colour other cells
    By Craigsim in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-16-2015, 06:09 AM
  2. Count Cells Based on Colour Determined by Conditional Formatting
    By RanCanMan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-19-2015, 09:45 AM
  3. [SOLVED] Conditional Formatting, problem with cell colour fill based on another cell value
    By first.officer in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-18-2013, 08:21 AM
  4. Count blank cells by colour based on conditional formatting
    By mb0202 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-03-2013, 07:37 AM
  5. [SOLVED] Change cell colour based on text being present - conditional formatting?
    By rob-the-randy-rascal in forum Excel General
    Replies: 8
    Last Post: 08-01-2013, 11:24 AM
  6. [SOLVED] Conditional Formatting (?) based upon another cells colour
    By bellevue in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-09-2012, 04:27 AM
  7. Replies: 4
    Last Post: 12-21-2011, 11:32 AM

Bookmarks

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 (?)
More Information
Template Usage (36):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (2)bbcode_code
  • (2)bbcode_quote
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (11)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (1)postbit_attachment
  • (11)postbit_legacy
  • (11)postbit_onlinestatus
  • (11)postbit_wrapper
  • (4)showthread_bookmarksite
  • (7)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
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
  • ./showthread.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_facebook.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_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • 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
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1