Formula sau VBA in Excel

[LS]

am o intrebare referitoare Excel:..
am o coloana in care sunt niste date, pe anumite coduri de culori..
spre ex, culoarea albastru (ColorIndex=37) reprezinta o chestie finalizata. cum fac o formula in excel sau vba-ul lui care sa imi numere toate celulele cu albastru de pe coloana respectiva?

exemplu elocvent: sa zicem coloana A25:A5000 are mai multe culori in ea, dar vreau sa imi numere (un count, countif, ceva) toate celulele care au culoarea albastru nu stiu care (ColorIndex = 37, de ex.)

mersi anticipat!

[LS]

err.. nu stie nimeni?

[LS]

ok, tot eu imi voi da raspunsul, asta pentru cine ar putea intampina problema pe care am avut-o eu.


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 collection. 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 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 value a a cell. InRange is the range of cells to examine, OfText indicates whether to return the ColorIndex of the Font (if True) or the Interior (if False).

Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the Interior
' (background) of a cell, or, if OfText is true, of the Font in the cell.
'
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange(1,1).Font.ColorIndex
Else
CellColorIndex = InRange(1,1).Interior.ColorIndex
End If

End Function

You can call this function from a worksheet cell with a formula like
=CELLCOLORINDEX(A1,FALSE)