Results 1 to 10 of 10
  1. #1
    5 Star Lounger Vincenzo's Avatar
    Join Date
    Mar 2004
    Posts
    654
    Thanks
    95
    Thanked 14 Times in 13 Posts

    Excel - sort or filter based on bold font

    I am seeing now that I will need to either filter or sort columns based on entries that have a bold face font. I do not see any way to do this. Is it possible?

    Thanks

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You would have to create a function to determine if the font is bold. Then use the function in an intermediate column to test for boldness. YOu can sort of filter on this column.

    For example add this code to a module:
    Code:
    Function IsBold(rCell As Range)
      IsBold = rCell.Font.Bold
    End Function
    This in a blank column use:
    =isbold(A2)

    It will return true or false. COpy it down the column and you can filter/sort on this.

    A way without the function would be to use conditional formatting. In the intermediate column enter a value and then use the results of that value to set the boldness. You would still filter/sort on the independent column and not the dependently bolded cells.

    Steve

  3. #3
    5 Star Lounger Vincenzo's Avatar
    Join Date
    Mar 2004
    Posts
    654
    Thanks
    95
    Thanked 14 Times in 13 Posts
    Quote Originally Posted by sdckapr View Post
    In the intermediate column enter a value and then use the results of that value to set the boldness. Steve
    Can you explain this in more detail?

    I assume the first option requires VBA which I cannot spend the time to learn right now, so I will go with the second option.

    Thanks

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Here is an example file. the first is explicitly formatted bold and the function indicates it. The 2nd uses cond formatting.

    Steve
    Attached Files Attached Files

  5. #5
    5 Star Lounger Vincenzo's Avatar
    Join Date
    Mar 2004
    Posts
    654
    Thanks
    95
    Thanked 14 Times in 13 Posts
    When I opened your file, I only had #NAME? errors in the IsBold column of the first sheet (I did not see the second sheet at first). Excel gave me a Macro security warning so I ok'ed it and then I got TRUE and FALSE in that column. But I do not see a macro listed. And when I try to reproduce this on my sheet it does not work. Is there a macro there or not?

    Then I noticed your second sheet, but I do not understand how you created it to get the results shown there.

    Thanks

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    A function is a macro. It is in the module in VB. It is the function I supplied earlier.

    I used conditional formatting. Select one of the cells in the table and press (format - conditional formatting...)

    See http://www.contextures.com/xlCondFormat01.html for a primer on conditional formatting. See http://www.contextures.com/xlCondFormat02.html for formatting based on another cell which is what I am doing. I make the cells bold based on having something in the cell in Col D.

    Steve

  7. #7
    5 Star Lounger Vincenzo's Avatar
    Join Date
    Mar 2004
    Posts
    654
    Thanks
    95
    Thanked 14 Times in 13 Posts
    I've been unable to understand what you've done on that sheet even after reading those primers. I cannot reproduce what you've done. Does it require knowledge of VBA? And what is the reason for the second sheet, your first sheet alone seems to do what I am looking for?

    Thanks

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The first sheet ("Explicit Formatting") uses a "UDF" (a user-defined function in VBA). It does not require knowledge of VB to use, since the function is already created. It does require that the function to be placed in a module of the workbook using the function and allowing macros to run in the workbook.

    The second sheet ("Cond Formatting") is an alternative approach which does not require the UDF and thus does not require a macro (some companies do not allow or limit macro use in spreadsheets). This one uses conditional formatting to make the cells bold instead of explicitly making them bold and reading the formatting.

    You don't need both schemes only the one that you think is better.

    What exactly do you mean by not reproduce the workbooks? Could you post an example and indicate what does not work?

    Steve

  9. #9
    5 Star Lounger Vincenzo's Avatar
    Join Date
    Mar 2004
    Posts
    654
    Thanks
    95
    Thanked 14 Times in 13 Posts
    OK I understand now what you did on the second sheet, that is not what I am looking for.


    Quote Originally Posted by sdckapr View Post
    The first sheet ("Explicit Formatting") uses a "UDF" (a user-defined function in VBA). It does not require knowledge of VB to use, since the function is already created. It does require that the function to be placed in a module of the workbook using the function and allowing macros to run in the workbook.

    Steve
    Can you explain how to do this?
    Thanks

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Alt-F11 opens up VBA
    In the project window pane on the left (looks like folders), select the "VBA Project" that matches your filename [It may or may not be the only one there]
    From the menu bar: Insert -Module
    This should open pane on the right side.
    Copy the UDF I posted and paste it into that pane
    Alt-Q closes VB and returns to excel

    Now you can use the UDF like the built-in formulas (If you are prompted to run macros, you must accept for the UDF to work)

    Steve

Posting Permissions

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