Format Calculation

  • Thread starter Thread starter Shawn
  • Start date Start date
S

Shawn

Here's an 'easy one' that I can't remember...

I have fields that I shade to grey when completed.
I want the shaded fields to automatically count as "1"
while clear fields count as "0", so I can keep a total of
data in the shaded areas.

What's the formula to get this to work?

Thanks!
 
Shawn, have a look here on how to count shaded cells
http://www.cpearson.com/excel/colors.htm
--
Paul B
Always backup your data before trying something new
Using Excel 97 & 2000
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
 
I'm not familiar with creating funcions in Excel. Is
there a process out there I can read/follow?
 
Shawn, To put in This function, from your workbook right-click the
workbook's icon and pick View Code. This icon is to the left of the "File"
menu this will open the VBA editor, in the left hand window click on your
workbook name, go to insert, module, and paste the code in the window that
opens on the right hand side, press Alt and Q to close this window and go
back to your workbook . If you are using excel 2000 or newer you may have
to change the macro security settings to get this to work.

Function CountByColor(InRange As Range, WhatColorIndex _
As Integer, Optional OfText As Boolean = False) As Long
'
' This function return the number of cells in InRange with
' a background color, or if OfText is True a font color,
' equal to WhatColorIndex.
'
Dim Rng As Range
Application.Volatile True

For Each Rng In InRange.Cells
If OfText = True Then
CountByColor = CountByColor - _
(Rng.Font.ColorIndex = WhatColorIndex)
Else
CountByColor = CountByColor - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
Next Rng

End Function

Then in a cell put something like this =COUNTBYCOLOR(A1:A10,16,FALSE) this
will count the number of cell with and fill color of gray 50% in the range
A1:A10, change to your range and if you are using another gray color change
the 16 to that color, gray 40% in 48 and gray 25% is 15


--
Paul B
Always backup your data before trying something new
Using Excel 97 & 2000
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
 
Back
Top