Microsoft Office Tutorials and References
In Depth Information
Using Ranges
A cell’s background color is not part of the Font object; it’s stored in the Interior
object. This function returns True if the upper-left cell of its argument is colored red
( vbRed is a built-in constant):
Function ISREDBKGRD(cell)
ISREDBKGRD = cell.Range(“A1”).Interior.Color = vbRed
End Function
The Columns and Rows properties
The Columns and Rows properties work with columns or rows in a range. For example, the
following function returns the number of columns in a range by accessing the Count property:
Function COLUMNCOUNT(rng)
COLUMNCOUNT = rng.Columns.Count
End Function
The EntireRow and EntireColumn properties
The EntireRow and EntireColumn properties enable you to work with an entire row or
column for a particular cell. The following function accepts a single cell argument and then uses the
EntireColumn property to get a range consisting of the cell’s entire column. It then uses the
Excel COUNTA function to return the number of nonempty cells in the column.
Function NONEMPTYCELLSINCOLUMN(cell)
NONEMPTYCELLSINCOLUMN = WorksheetFunction.CountA(cell.EntireColumn)
End Function
You cannot use this function in a formula that’s in the same column as the cell argument. Doing
so will generate a circular reference.
The Hidden property
The Hidden property is used with rows or columns. It returns TRUE if the row or column is
hidden. If you try to access this property for a range that does not consist of an entire row or
column, you get an error. The following function accepts a single cell argument and returns TRUE if
either the cell’s row or the cell’s column is hidden:
Function CELLISHIDDEN(cell)
If cell.EntireRow.Hidden Or cell.EntireColumn.Hidden Then
CELLISHIDDEN = True
Else
CELLISHIDDEN = False
End If
End Function
 
Search JabSto ::




Custom Search