ID: Q142122
The information in this article applies to:
In Microsoft Excel, there is no built-in feature to allow you to search on the characteristics of a cell. However, you can create a Microsoft Visual Basic for Applications procedure that allows you to search this way.
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft Support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:
http://www.microsoft.com/support/supportnet/refguide/
1. Open a new workbook.
2. Enter the following on Sheet1.
A1: 1 B1: 2 C1: 3 D1: 4
the Formatting toolbar.
Format menu, clicking the Font tab, and then clicking Single in the
Underline drop-down.
Format menu, clicking the Font tab, and clicking Double from the
Underline drop-down. Also, format this cell as Italic by clicking the
Italic button on the Formatting toolbar.
In Microsoft Excel 97, click the Tools menu, point to Macro, and
click Visual Basic Editor. Then, click Module on the Insert menu.
Sub Checking_the_Format()
Range("A1:D1").Select
' Use a loop to check each cell in the selection.
For Each cel In Selection
' The next line tests for the bold property of the font.
If cel.Font.Bold = True Then MsgBox "This cell is bold: " _
& cel.Address
' The following line will display the font style of the current
' it is not bold or italic.
If cel.Font.FontStyle <> "bold" Or _
cel.Font.FontStyle <> "italic" Then _
MsgBox cel.Font.FontStyle
' This example will check the underline of the font in the
' current cell to see if it is a single line (xlsingle).
If cel.Font.Underline = xlSingle Then _
MsgBox "Here is the single underline: " _
& cel.Address
' Finish the loop.
Next cel
' The next line sets the weight and color of the left border of
' the current selection.
With Range("B3").Borders(xlLeft)
.Weight = xlMedium
.ColorIndex = 3
End With
Range("A10").Select
End Sub
When the macro finds the specified formatting, the cell address appears in a message box.
Note that although this macro displays message boxes to indicate the result of the code, you can customize this code to perform a variety of actions when the correct cells are located.
The following tables describe properties of the Font object that you can test for in a Visual Basic macro.
Properties That Return a Boolean Value (True Or False):
For this property True means this False means this
-------------------------------------------------------------
Bold Font is bold Font is not bold
Italic Font is italic Font is not italic
OutlineFont Font is outline Font is not outline Font
font
Shadow Font is shadow Font is not shadow Font
font
Strikethrough Font is Font is not formatted with
formatted with strike through
strike through
Subscript Font is Font is not subscript
subscript
Superscript Font is Font is not superscript
superscript
For this property Test for this value
------------------------------------------------------------------
Color The font color as an RGB value
ColorIndex The color as an index number from
the current color palette
FontStyle The font style as a string (for
example, "Bold")
Name The name for the font (for example, "Arial")
Size The size of the font (for example, 12)
Underline The type of underline applied to text in a
the cell (for example, "xlSingle")
For this property Test for this value
-----------------------------------------------------------------
Color The border color as an RGB value
ColorIndex The color as index of the current color palette
Linestyle The line style (for example "xlContinuous")
Weight The weight of the line (for example "xlHairline")
The Interior Object is used to detect the color or pattern in the cell itself. You can test the Interior object for the following properties.
For this property Test for this value
---------------------------------------------------------------
Color The primary color
ColorIndex The color as index of current color palette
Pattern The pattern (for example, "xlChecker")
PatternColor The pattern color as a RGB value
PatternColorIndex The color as index of current color palette
In Microsoft Excel 7.0 and 97, to find more information about the Font object, the Border object, or the Interior object, click Answer Wizard on the Help menu and type the appropriate word:
Font Object
-or-
Border Object
-or-
Interior Object
Font Object
-or-
Border Object
-or-
Interior Object
Keywords : kbprg kbualink97 kbdta kbdtacode PgmHowto KbVBA
Version : WINDOWS:5.0,5.0c,7.0,97; MACINTOSH:5.0,5.0a
Platform : MACINTOSH WINDOWS
Issue type : kbhowtoLast Reviewed: May 17, 1999