IF Formula
I have several cells with specific colors. Is there a way to count these
cells? |
IF Formula
Chip has stuff on this at www.cpearson.com
best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Cathie G" wrote in message ... I have several cells with specific colors. Is there a way to count these cells? |
IF Formula
Hi,
Here is a fancy version of code to count cells that have various formats such as color Function CountFormats(R As Range, E As Range) As Integer Dim cell As Range Dim Total As Integer Dim T As Boolean Set S = E.Cells(1, 1) Total = 0 For Each cell In R T = True With cell If .Font.ColorIndex < S.Font.ColorIndex Then T = False If .Interior.ColorIndex < S.Interior.ColorIndex Then T = False If .Font.Bold < S.Font.Bold Then T = False If .Font.Italic < S.Font.Italic Then T = False If .Font.Underline < S.Font.Underline Then T = False End With If T = True Then Total = Total + 1 End If Next cell CountFormats = Total End Function You would enter the custom function in the spreadsheet =CountFormats(A1:F13,K1) And the function would count the number of cells in the range A1:F13 that match the format of K1 for the formmating options in the code. You can take out IF .Font statements and leave just the Interior.Color one. If this helps, please click the Yes button. Cheers, Shane Devenshire "Cathie G" wrote: I have several cells with specific colors. Is there a way to count these cells? |
All times are GMT +1. The time now is 08:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com