Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have seen it somewhere but unable to recall how to do. In Gray area of attached ws, I am looking for a countif/sumif based on cell OR font color.
    Attached Files Attached Files
    Regards
    Prasad

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    See Color Functions In Excel on Chip Pearson's website.

  3. #3
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='789685' date='19-Aug-2009 11:03']See Color Functions In Excel on Chip Pearson's website.[/quote]
    Hans, I have gone through the site before disturbing you. I thought, I missed something and visited again on your direction, but failed to get a proper solution. Actually, I am looking for a formula instead of Code and getting #Name error on applying given formula. Am i missing something OR some additional utility is required? I am not sure.
    Regards
    Prasad

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The web page I referred you to contains a set of VBA functions that can be used in worksheet formulas to work with colors. The web page gives examples of the use of each of those functions.
    You have to download the entire module and import it into your workbook.
    This can NOT be done without VBA.

  5. #5
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='789693' date='19-Aug-2009 12:26']The web page I referred you to contains a set of VBA functions that can be used in worksheet formulas to work with colors. The web page gives examples of the use of each of those functions.
    You have to download the entire module and import it into your workbook.
    This can NOT be done without VBA.[/quote]
    Some more questions:
    1. It is summing up the value based on cell color. But how to set another condition?
    2. Is it possible to use the code as Add-in? I dont have a personal macro book and copying code everytime is little tedious.

    Attached Files Attached Files
    Regards
    Prasad

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    1. In cell C2:

    =SUMPRODUCT((ColorIndexOfRange($A$2:$A$7,FALSE,0)= colorindexofonecell(C$1,FALSE,0))*($A$2:$A$7=C$1)* $B$2:$B$7)

    2. You can import the module into an empty workbook and save it as an add-in (.xla), or you can import the module into an existing add-in.

  7. #7
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='789719' date='19-Aug-2009 17:21']1. In cell C2:

    =SUMPRODUCT((ColorIndexOfRange($A$2:$A$7,FALSE,0)= colorindexofonecell(C$1,FALSE,0))*($A$2:$A$7=C$1)* $B$2:$B$7)

    2. You can import the module into an empty workbook and save it as an add-in (.xla), or you can import the module into an existing add-in.[/quote]

    speachless. I have tried everything including SUMPRODUCT, but in a different way. Thanks is somehow meaningless & I dont have an appropriate phrase to express gratitute. Regarding No. 2, I have saved the code as .xla but it is not working.

    [attachment=85171:untitled.JPG]
    Attached Images Attached Images
    Regards
    Prasad

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You must delete the line

    Attribute VB_Name = ...

    (If you use File | Import File..., that line will not be imported)

  9. #9
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='789724' date='19-Aug-2009 17:43']You must delete the line

    Attribute VB_Name = ...

    (If you use File | Import File..., that line will not be imported)[/quote]

    Regards
    Prasad

  10. #10
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    A late hour querry...

    How is it possible to make any code available every time opening a new wb? (Pl dont laugh at me, it is an early stage for me to get familier with codes)
    Regards
    Prasad

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Do you want a macro to run every time the user creates a new workbook?

  12. #12
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='790094' date='21-Aug-2009 17:45']Do you want a macro to run every time the user creates a new workbook?[/quote]
    not exactly. What I want an existing macro to store in workbook on creation. It should be optional to run.
    Regards
    Prasad

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you want a macro to be available in all workbooks it is more efficient to use an add-in (.xla) then to store the macro in every workbook.

    If you do want a macro to be stored in the workbooks themselves, you can save a workbook that contains the macro (and whatever else you want) as an Excel template (.xlt). When you create a new workbook from the template, the macro will be part of the new workbook.

  14. #14
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='790100' date='21-Aug-2009 17:59']If you want a macro to be available in all workbooks it is more efficient to use an add-in (.xla) then to store the macro in every workbook.

    If you do want a macro to be stored in the workbooks themselves, you can save a workbook that contains the macro (and whatever else you want) as an Excel template (.xlt). When you create a new workbook from the template, the macro will be part of the new workbook.[/quote]
    I understand & prefer the concern of using an add-in instead of store the macro in every workbook. The problem is that most reports created are meant for external customer and they may/may not have all the add-ins I am using. It is just a thought to save macro in wb as there is no other way (so far my knowledge permits) to make files worth-while, having add-in support.

    Thanks again for your guidance.
    Regards
    Prasad

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In that case, I'd create a template (.xlt) for your reports that contains the macros you need. When you need to create a new report, create it from the template instead of a blank workbook.

Page 1 of 2 12 LastLast

Posting Permissions

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