Microsoft KB Archive/213622

= XL2000: Macro to Search for Cell Formats: Font, Border, and Interior =

Article ID: 213622

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q213622





SUMMARY
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.



MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements. To create a sample Visual Basic macro that selects a range and searches it for specific formatting:  Open a new workbook.  Type the following data on Sheet1:      A1: 1  B1: 2   C1: 3   D1: 4  Format cell B1 with bold formatting by clicking the Bold button on the Formatting toolbar. Format cell C1 with a single underline by clicking Cells on the Format menu, clicking the Font tab, and then clicking Single in the Underline list. Format cell D1 with a double underline by clicking Cells on the Format menu, clicking the Font tab, and clicking Double in the Underline list. Also, format this cell as italic by clicking the Italic button on the Formatting toolbar. Insert a new module sheet:

On the Tools menu, point to Macro, and then click Visual Basic Editor. Then, click Module on the Insert menu.</li>  Type the following macro on the module sheet: 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 </li> Return to Sheet1 and run the macro. To do this, point to Macro on the Tools menu and then click Macros. Click to select the macro and then click Run.</li></ol>

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.

Testing for Properties of the Font Object
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)
<pre class="fixed_text">  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   Font is not outline Font

Shadow            Font is shadow Font    Font is not shadow Font

Strikethrough     Font is formatted      Font is not formatted with with strike through   strike through

Subscript         Font is subscript      Font is not subscript

Superscript       Font is superscript    Font is not superscript

Properties That Return a String or Numeric Value
<pre class="fixed_text">  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")

Testing for Properties of the Border Object
<pre class="fixed_text">  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")

Testing for Properties of the Interior Object
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. <pre class="fixed_text">  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

<div class="references_section">