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

ID: Q142122

The information in this article applies to:

Microsoft Excel for the Macintosh, versions 5.0, 5.0a
Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel 97 for Windows

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, 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 a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

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/overview/overview.asp

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:

In Microsoft Excel 97, point to Macro on the Tools menu, and click Visual Basic Editor. Then, click Module on the Insert menu.

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

Return to Sheet1 and run the macro.

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)


   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

Properties That Return a String or Numeric Value


   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


   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.


   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

REFERENCES

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

In Microsoft Excel 5.0, for more information about the Font object, the Border object, or the Interior object, choose the Search button in Visual Basic Help, and type the appropriate word:

Font Object

-or-

Border Object

-or-

Interior Object

Additional query words: 5.00a 5.00c 8.00 XL97

Keywords : kbprg kbualink97 kbdta kbdtacode PgmHowto KbVBA
Version : MACINTOSH:5.0,5.0a; WINDOWS:5.0,5.0c,7.0,97
Platform : MACINTOSH WINDOWS
Issue type : kbhowto