More Information

Topic

New Reply

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.

Reply | Quote

Replies

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.

Public Function bIsBold(oCell As Range) As Boolean
    bIsBold = oCell.Font.Bold
End Function

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.

Reply | Quote

A tall order but something like the following might get you started :-

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

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 :-

	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

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

Conditional formatting lives under the Format menu.

Reply | Quote

Oops, my mistake.

Reply | Quote