XL: Macro to Search for Cell Formats: Font, Border, and InteriorLast reviewed: February 3, 1998Article ID: Q142122 |
The information in this article applies to:
SUMMARYIn 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 INFORMATIONMicrosoft 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 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 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/default.aspUse the following steps to create a sample Visual Basic macro that selects a range and searches it for specific formatting.
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 ObjectThe 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 superscriptProperties 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 ObjectThe 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 REFERENCESIn 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 ObjectFor more information in Microsoft Excel 5.0 about the Font object, the Border object, or the Interior object, choose the Search button in Visual Basic Help type the appropriate word:
Font Object -or- Border Object -or- Interior Object |
Additional query words: 5.00 5.00a 5.00c 7.00 8.00 97 XL97
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |