Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    May 2001
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    More Information

    Using Excel 97. Looking for formulas providing more information on cells. The existing formulas don't provide enough. I would like to return background color, highlighted color, if bold, etc. Has anyone created a custom function that provided this info?

    Thanks.

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

    Re: More Information

    Writing VBA code to do what you are asking is fairly simple. For example, the following VBA function will return True or False depending on the Bold/Not Bold status of the cell passed as a parameter.

    <pre>Public Function bIsBold(oCell As Range) As Boolean
    bIsBold = oCell.Font.Bold
    End Function
    </pre>


    However, we really need to know exactly what you want to accomplish. If you use this function in a worksheet cell, it will initially show the correct value in the cell. However, if you change the Bold status of the target cell, the cell containing this formula will not be automatically updated since a change in cell properties does not trigger a recalculate. You would have to manually trigger a recalculate to get the correct value displayed. If you want to use this in other VBA code, then the above will work fine.
    Legare Coleman

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: More Information

    A tall order but something like the following might get you started :-<pre>Function CellInfo(rng As Range, Info As Integer) As Variant
    Application.Volatile True
    CellInfo = CVErr(xlErrNA)
    Select Case Info
    Case 1
    CellInfo = rng.Interior.ColorIndex 'Return Number
    If CellInfo < 0 Then CellInfo = 0
    Case 2
    CellInfo = rng.Interior.PatternColorIndex 'Return Number
    If CellInfo < 0 Then CellInfo = 0
    Case 3
    CellInfo = rng.Font.ColorIndex 'Return Number
    If CellInfo < 0 Then CellInfo = 0
    Case 4
    CellInfo = rng.Font.Italic 'Return True or False
    Case 5
    CellInfo = rng.Font.Bold 'Return True or False
    Case 6
    CellInfo = rng.Font.Superscript 'Return True or False
    Case 7
    CellInfo = rng.Font.Subscript 'Return True or False
    Case 8
    CellInfo = rng.Font.Name 'Return String
    Case 9
    CellInfo = rng.Font.FontStyle 'Return String
    End Select
    End Function</pre>


    There are many other possibilities, but the line must be drawn somewhere.

    The function takes 2 arguments, A Cell address and a number to indicate the info required. The possible numbers are :-<pre> 1 = Cell Color, returns a number (0 to 56)
    2 = PatternColor, as 1
    3 = Font Color, as 1
    4 = Italic, Returns True or False
    5 = Bold, as 4
    6 = Superscript, as 4
    7 = Subscript, as 4
    8 = Font Name, String
    9 = Font Style, String</pre>

    Where a value is less than 0(the colors) I have set them to 0, as it indicates that the setting is either none or automatic etc.

    Example: to get the Font Color in A1 you could have =(CellInfo,A1,3)

    A caveat : Changes to formatting does not cause excel to recalculate so until you force a calculation any results from the above should not be relied on. I have tried to at least have Excel update the functions any time any other event cause the sheet to recalculate.

    This is only one approach, and there are other ways of achieving the same type of results.

  4. #4
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More Information

    You might even try using the 'Conditional Formatting' under the 'Tools' menu. You can use that like an IF function to provide; colour; bold; italic, whatever, according to preset parameters.

  5. #5
    New Lounger
    Join Date
    May 2001
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More Information

    Thanks to Legare and Andrew for your help.
    Egg 'n' Bacon - I do not see conditional formatting under the tools menu. Is this an Excel 97 feature? Do I need to install an add-in? Are you referring to the conditional sum add-in?

  6. #6
    New Lounger
    Join Date
    May 2001
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More Information

    Conditional formatting lives under the Format menu.

  7. #7
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More Information

    Oops, my mistake.

Posting Permissions

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