How to count coloured cells and then work out a percentage.

[danbates]

Hi,

I would like to work out a percentage of the completed checks on a daily basis. Updates when I open the file.

I have red, blue, green and yellow cells in col B and if the checks have been completed then the operator signs them off in col H.

I already have a code that finds today's date and then depending on the time it then finds the correct shift, which then becomes the active row.

What I am thinking is a code or formula to count the coloured cells (col B) individually that are above the active row and then count the the coloured cells that have been signed off (col H).

Then work out the percentage of checks that have been completed.

I hope this makes sense and any help would be appreciated.

Thanks

Dan

 

[Special-K99]

As far as I know formulas cannot detect the colour of a cell unless that cell's colour has been set by Conditional Formatting, in which case just use the same conditions.
So it would most likely need to be done via VBA.

UPDATE: This may work...

Excel formula to get cell color

I would like to know if we can find out the Color of the CELL with the help of any inline formula (without using any macros) I'm using Home User Office package 2010.

stackoverflow.com

 

[danbates]

Hi,

Thank you for your suggestion and you're right, it seems to be a bit of both and I've been searching online and I have found this:

Function Color_Cell_Count(ColorCell As range, DataRange As range)
Dim Data_Range As range
Dim Cell_Color As Long
Cell_Color = ColorCell.Interior.ColorIndex
For Each Data_Range In DataRange
If Data_Range.Interior.ColorIndex = Cell_Color Then
Color_Cell_Count = Color_Cell_Count + 1
End If
Next Data_Range
End Function

and then my formula is:
=Color_Cell_Count(L1,$B$5:$B$736).

It works well but I still want to run it from the activecell. So I was wondering if I can swap the range B5:B736 with the following macro?

Sub aaaa()
range(ActiveCell, ActiveCell.End(xlUp)).Select
End Sub

any help would be appreciated.

Thanks Dan