Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Feb 2001
    Location
    North Carolina, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Identify 'Color as Displayed' (Excel 2002 SP-2)

    Identify 'Color as Displayed' (Excel 2002 SWP-2)
    I use a lot of "Cell, format, Custom" w/n spreadsheets to create reports like for
    example

    Example 1:
    --if the cell value is less than 31.5 make font red, if greater than 59.49 make font red - else font is black
    so font is RED if cell value as displayed is for example 30 or 61
    or black font if cell value is say 40

    or

    Example 2:
    -- if cell value is less that 54.5 or greater than 89.49 make font black and display value with "*" (asterisk) appended to end
    otherwise just show value as black
    so font is always BLACK with value displayed as for example 50* , 91* , or 80


    I know how to use code to identify the values in Example 2 to convert the font to
    BOLD and Underscore in a report:

    For Each zCell In Selection
    If Right(zCell.Text, 1) = "*" Then 'does display value have "*"
    ' .... make cell font BOLD and Underlined in Repts.....
    zCell.Font.Bold = True
    zCell.Font.Underline = True
    End If
    Next zCell

    However, I do not know how to use a macro to identify the values that appear with RED font
    as formatted in Example 2 !!!
    Is there a way to identify the font color as displayed and then if it is red to make the
    cell value BOLD and UnderLined ????
    (I have not posted for a while and do not know how to get my examples to appear properly...
    so I have tried to use words....)
    Thanks for any help.
    Phyl

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identify 'Color as Displayed' (Excel 2002 SP-2)

    If the cell is formatted using the Format menu or from VBA code, then you can use:

    <code>
    If zCell.Font.Colorindex = 3 Then
    </code>

    The 3 in the above code assumes that you are using the default color pallett.

    If the cell is formatted by Conditional Formatting, then you need to check the same conditions that are specified in the Conditional Format formula.
    Legare Coleman

  3. #3
    Lounger
    Join Date
    Feb 2001
    Location
    North Carolina, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identify 'Color as Displayed' (Excel 2002 SP-2)

    Legare,
    Thanks for your reply. I tried the following and it did not change the font to bold or underscored?

    Sub RedBoldUnderscore()
    'BOLD and Underscore in a report:
    Dim zCell ' As Range

    For Each zCell In Selection
    If zCell.Font.ColorIndex = 3 Then
    ' .... make cell font BOLD and Underlined in Repts.....
    zCell.Font.Bold = True
    zCell.Font.Underline = True
    End If
    Next zCell
    End Sub

    The cells have underlying Custom Number Formats like maybe:

    " make font Red [<19.5]0; make font red [>55.49]0; make font black 0 '''
    (sorry I can show actual input -- it keeps changing when I submit the query to you...)

    but are different for each cell. (formats created by Format, Cell, Custom...)
    What am I doing wrong?
    Thanks again for your suggestions.
    Phyl

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Identify 'Color as Displayed' (Excel 2002 SP-2)

    I would use conditional formatting for this instead of a custom number format and VBA code.
    See the attached workbook. Select Format | Conditional Formatting... to see how it is set up for A1:A11.

  5. #5
    Lounger
    Join Date
    Feb 2001
    Location
    North Carolina, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identify 'Color as Displayed' (Excel 2002 SP-2)

    HansV,

    Thanks for suggestion. I will look at your example a little later when time permits.
    However, there are about 150 - 200 workbooks using this type of custom format. Each having
    8 to 13 different values in cells (plust 1 worksheet per month)-- that then feed weekly and monthly reports (using same # formats).
    Looks like lots of manual work for me. These workbooks were originally created before conditional formatting
    was available to me (back when using Windows 3.1 !)...
    Also, am curious... would using conditional formats increase the size of the workbooks vs. what I have today?
    I could test this - but just thought you may know.

    From your suggestion - am I to assume that there is no way to do it in my reports macros as I was trying to do?
    Phyllis

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Identify 'Color as Displayed' (Excel 2002 SP-2)

    Conditional formatting takes up some space, but so do custom number formats. I don't know which of the two adds the most bulk to a workbook.

    What you want could be done using code, but it would be tedious and slow - you'd have to look at the custom format for each cell, parse the format string into its constituent parts, interpret them, and compare them to the cell's value to determine the color actually being displayed.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identify 'Color as Displayed' (Excel 2002 SP-2)

    AFAIK, the only way to determine the color from a custom format (like a conditional format) in code is to check the same conditions that the custom format is using. I don't know another way to determine the display color directly. I think that Hans' conditional format suggestion would be the best way to handle this.
    Legare Coleman

  8. #8
    Lounger
    Join Date
    Feb 2001
    Location
    North Carolina, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identify 'Color as Displayed' (Excel 2002 SP-2)

    Legare,
    Thanks - though this is not what I wanted to hear... Getting lazy in my old age.
    So - can you point me to someplace I might find info on doing conditional formatting from a macro???
    This might make my task easier to swallow...
    Phyl

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Identify 'Color as Displayed' (Excel 2002 SP-2)

    If you search this forum (or Google) for FormatConditions you'll find several examples.

Posting Permissions

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