This page has been replaced. Click here to go to the new page.
Functions For Working With Cell Colors
Excel does not have any built-in worksheet functions for working with the colors of cells or fonts. If you want to read or test the color of a cell, you have to use VBA procedure. This page describes several functions for counting and summing cells based on the color of the font or background. All of these functions use the ColorIndex property. Excel worksheets can't have the vast amount of colors that other applications support. In Excel, you are limited to the 56 colors that are part of the Color Pallet for the workbook. You may assign any color you want to an entry in the Color Pallet, but each workbook is limited to a total of 56 different colors.The ColorIndex of a range is simply the offset of the color into the Color Pallet table. For example, ColorIndex 6 is simply the sixth entry in the Color Pallet. You can change the default colors in the Color Pallet of a workbook by using the Colors array. For example, to change ColorIndex 6 from yellow (the default) to red, use the following code: ThisWorkbook.Colors(6) = RGB(255,0,0) If you use the Color property of a cell's Font or Interior, Excel will change the value you assign to the closest match color that exists in the current Color Pallet. NOTE: When you change the background or font color of a cell, Excel does not consider this to be changing the value of the cell. Therefore, it will not recalculate the worksheet, nor will it trigger a Worksheet_Change event procedure. This means that the values returned by these functions may not be correct immediately after you change the color of a cell. They will not return an updated value until you recalculate the worksheet by pressing ALT+F9 or by changing the actual value of a cell. There is no practical work-around to this. You could use the Worksheet_SelectionChange event procedure to force a calculation, but this could have a serious and detrimental impact on performance. NOTE: These functions will not detect colors that are applied by Conditional Formatting. They will read only the default colors of the cell and its text. For information about returning colors in effect by conditional formatting, see the Conditional Formatting Colors page. It is important to remember that if a cell has no color assigned to it, and therefore appears to be white, the ColorIndex is equal to the constant xlColorIndexNone, or -4142. It does not equal 2, the default ColorIndex value for white. Similarly, text that has not been assigned a color, and therefore appears to be black, has a ColorIndex value equal to the constant xlColorIndexAutomatic, or -4105. It does not equal 1, the default ColorIndex value for black.The sections below describe a number of VBA functions for working with cell colors. Returning The ColorIndex Of A Cell The following function will return the ColorIndex property of a cell. InRange is the cell to examine, OfText indicates whether to return the ColorIndex of the Font (if True) or the Interior (if False). If InRange contains more than one cell, the first cell (InRange(1,1)) of the range is tested. Function CellColorIndex(InRange As Range, Optional
_ You can call this function from a worksheet cell with a formula like Counting Cells With A Specific Color The following function will return the number of cells in a range that have either an Interior (background) or Font of a specified color. InRange is the range of cells to examine, WhatColorIndex is the ColorIndex value to count, and OfText indicates whether to return the ColorIndex of the Font (if OfText is True) or the Interior (if OfText is False or omitted). Function CountByColor(InRange As Range,
_ Summing The Values Of Cells With A Specific Color The following function will return the sum of cells in a range that have either an Interior (background) or Font of a specified colorindex. InRange is the range of cells to examine, WhatColorIndex is the ColorIndex value to count, and OfText indicates whether to return the ColorIndex of the Font (if True) or the Interior (if False). Function SumByColor(InRange As Range, WhatColorIndex As Integer, _ You can call this function from a worksheet cell with a formula like
Summing The Values Of Cells Based On The Color Of Other Cells The following function will return the sum of cells in a range which correspond to cells in another range that have either an Interior (background) or Font of a specified color. InRange is the range of cells to examine, WhatColorIndex is the ColorIndex value to count, SumRange is the range of value to sum, and OfText indicates whether to return the ColorIndex of the Font (if True) or the Interior (if False). Function SumIfByColor(InRange As Range,
_ Getting The Range Of Cells With A Specific Color The following function will return a Range object consisting of those cells in a range that have either an Interior (background) or Font of a specified color. InRange is the range of cells to examine, WhatColorIndex is the ColorIndex value to count, and OfText indicates whether to use the ColorIndex of the Font (if OfText is True) or the Interior (if OfText False or omitted). This function uses the AddRange function to combine two ranges into a single range, without the possible problems of the Application.Union method. See AddRange, below, for more details about this function. Function RangeOfColor(InRange As Range,
_ The following function will return the address, as a string, of the range returned by RangeOfColor. Function AddressOfRangeOfColor(InRange As Range, _ Getting Range Of A Cell With A Specific Color The following function will return a Range object consisting of the cell in a range that has either an Interior (background) or Font of a specified color. InRange is the range of cells to examine, WhatColorIndex is the ColorIndex value to count, FindWhich indicates which cell to return, and OfText indicates whether to return the ColorIndex of the Font (if True) or the Interior (if False). The value of FindWhich can be 0 to return the address of last cell with the specified color, or any positive integer to return that occurance (e.g., 3 to return the third occurance). Function FindColor(InRange As Range, WhatColorIndex As Integer, _ The following function will return the address, as a string, of the range returned by . Function AddressOfFindColor(InRange As Range,
_ AddRange The following function will return a Range object that is the logical union of two ranges. Unlike the Application.Union method, AddRange will not return duplicate cells in the result. For example, Application.Union(Range("A1:B3"), Range("B3:D5")).Cells.Count will return 15, since B3 is counted twice, once in each range. AddRange(Range("A1:B3"), Range("B3:D5")).Cells.Count willl return 14, counting B3 only once. Function AddRange(ByVal Range1 As Range,
_ Sorting By Color For information about sorting cells based on colors, please read Sorting By Color.
A color is defined by a number made up of the Red, Green, and Blue components of the color. To convert the individual components to a color value, you can use the VBA function RGB. For example, ActiveCell.Interior.Color
= RGB(100,123, 50) However, there is no built-in
method to break out the individual color components from a color value.
The procedure below will accomplish this. Sub GetRGB(RGB As Long, ByRef Red As Integer, _ Notice that the Red, Green, and Blue variables are passed by reference. The procedure will put the color values in these variables. For example, you can use this procedure as follows: Dim R As Integer |
||
Other Color Functions The SumByColor function can easily be adapted to AverageByColor, MaxByColor, and MinByColor, as shown below: Function AverageByColor(InputRange As Range, ColorIndex As Integer, _ OfText As Boolean) As Variant Dim Rng As Range Dim Total As Double Dim N As Long For Each Rng In InputRange.Cells If OfText Then If Rng.Font.ColorIndex = ColorIndex Then If IsNumeric(Rng.Value) Then Total = Total + Rng.Value N = N + 1 Else AverageByColor = CVErr(xlErrNum) Exit Function End If End If Else If Rng.Interior.ColorIndex = ColorIndex Then If IsNumeric(Rng.Value) Then Total = Total + Rng.Value N = N + 1 Else AverageByColor = CVErr(xlErrNum) Exit Function End If End If End If Next Rng If N = 0 Then AverageByColor = CVErr(xlErrDiv0) Else AverageByColor = Total / N End If End Function Function MaxByColor(InputRange As Range, ColorIndex As Integer, _ OfText As Boolean) As Variant Dim Rng As Range Dim Max As Double For Each Rng In InputRange.Cells If OfText Then If Rng.Font.ColorIndex = ColorIndex Then If IsNumeric(Rng.Value) Then If Rng.Value > Max Then Max = Rng.Value End If End If End If Else If Rng.Interior.ColorIndex = ColorIndex Then If IsNumeric(Rng.Value) Then If Rng.Value > Max Then Max = Rng.Value End If End If End If End If Next Rng MaxByColor = Max End Function Function MinByColor(InputRange As Range, ColorIndex As Integer, _ OfText As Boolean) As Variant Dim Rng As Range Dim Min As Double: Min = 1E+301 For Each Rng In InputRange.Cells If OfText Then If Rng.Font.ColorIndex = ColorIndex Then If IsNumeric(Rng.Value) Then If Rng.Value < Min Then Min = Rng.Value End If End If End If Else If Rng.Interior.ColorIndex = ColorIndex Then If IsNumeric(Rng.Value) Then If Rng.Value < Min Then Min = Rng.Value End If End If End If End If Next Rng MinByColor = Min End Function |
|
|
|