Calculating percentage based on cell color?

[cmlloveless]

I have another task that I have been asked to add to my wonderful training spread sheet. As personnel expire on their annual/biannual/semiannual training the cells now turn red.

Is it possible to calculate a percentage off of this? I need to know a percentage of how many cells are expired or red in the spread sheet. That way I don't have to keep manually calculating a "readiness" percentage everytime they ask for it.

So lets say 10 cells are red (which meand they have expired) and the remaining 130 cells are either green or yellow (which means they are not expired), then we are roughly at 93% ready or complient on training.

 

[beedistinct]

I assumed that the data where you wish to find the number of colored cells is in column D and that the column A has no cells as empty like the serial number so that I can use it to find the last row that has valid data.

Dim Count As Integer
Dim FirstRow As Integer
Dim LastRow As Integer

FirstRow = 4  ' Put the number of the row where your colored data starts
LastRow = Range("A" & Rows.Count).End(xlUp).Row

Count = 0
LoopCounter = 0

Do Until LoopCounter > LastRow
    Range("D" & (FirstRow + LoopCounter)).Select      ' Change column D to the column name that has your target data
    If Selection.Interior.Color = 192 Then    ' This one is looking for dark red
        Count = Count + 1
    End If
    LoopCounter = LoopCounter + 1
Loop

MsgBox ("The count of red colored cells is : " & Count)

MsgBox ("The percentage of red colored cells is : " & Round((Count / (LoopCounter - 1)) * 100, 2) & "%")

 

[Ruddles]

Remind me: are the cells changing colour because of conditional formatting or is there some VBA code which does it?

When you check the colour of any cell, you get its 'base' colour before the conditional formatting is applied, so if it starts out white and it turns red because of conditional formatting, it checks out as being white.

If you want to count cells which have changed colour because of conditional formatting, then you don't try to count their colour because that doesn't work, you have to count the properties of the cells which trigger the conditional formatting. So if cells turn red because the dates in them have expired, you have to count cells where the dates have expired.

I've tried to explain this several times and I've never really been happy with the way I've explained it!

 

[cmlloveless]

That is exactly what it is. The cells originally green and have a conditional format that turns them yellow 30 days before they expire and red when they have already expired. I also have the cells conditioned to turn red when they are blank.

So yes I need to calculate a percent based off how many are expired compared to the grand total of cells with data.

These are the conditional formats for each cell (give or take the number of days since some expire in 365 days and others in 183 and 730, but the overal formula is the same):

*=ISBLANK($0$0)
*=AND($R$4>0,$R$4+365< TODAY())
*=AND($R$4>0,$R$4+335< TODAY())

 

[beedistinct]

Remind me: are the cells changing colour because of conditional formatting or is there some VBA code which does it?

When you check the colour of any cell, you get its 'base' colour before the conditional formatting is applied, so if it starts out white and it turns red because of conditional formatting, it checks out as being white.

If you want to count cells which have changed colour because of conditional formatting, then you don't try to count their colour because that doesn't work, you have to count the properties of the cells which trigger the conditional formatting. So if cells turn red because the dates in them have expired, you have to count cells where the dates have expired.

I've tried to explain this several times and I've never really been happy with the way I've explained it!

When I was writing the code, I knew it cannot be as easy as I thought it is.
Thanks for explaining it so clearly!

 

[Ruddles]

When I was writing the code, I knew it cannot be as easy as I thought it is.

It's one of those things which you either know, or you spend hours trying to figure out why your code doesn't work!

 

[Ruddles]

That is exactly what it is. The cells originally green and have a conditional format that turns them yellow 30 days before they expire and red when they have already expired. I also have the cells conditioned to turn red when they are blank.

Maybe you can adapt one or more of these to suit...

If you have dates in R1:R20 (for example) and you want to know how many of them have expired:-

=COUNTIF(R1:R20,">"&NOW())

How many are within 30 days of today's date:-

=COUNTIF(R1:R20,">"&NOW()+30)

Percentage of dates which have expired:-

=COUNTIF(R1:R20,">"&NOW())/COUNT(R1:R20)

Percentage of dates which have expired or will expire within 30 days:-

=COUNTIF(R1:R20,">"&NOW()+30)/COUNT(R1:R20)

Percentage of dates which haven't yet expired but will expire within 30 days:-

=(COUNTIF(R1:R20,">"&NOW())-COUNTIF(R1:R20,">"&NOW()+30))/COUNT(R1:R20)

 

[cmlloveless]

Maybe you can adapt one or more of these to suit...

Percentage of dates which have expired or will expire within 30 days:-

=COUNTIF(R1:R20,">"&NOW()+30)/COUNT(R1:R20)

This one looks like it should work... I will try it out. I'm assuming I can change the 30 to any number I want such as 365 since they expire in 365 days from the date that is typed into the cell?

Also the "NOW" in the formula, is that refereing to the date I have typed in the cell? Would it work to replace it with "TODAY" so that it goes by the date I have typed into the cell?

 

[cmlloveless]

Hmmm... I have tried all of the formulas and none of them did anything. This is the last on I put in, let me know if I am doing something wrong here:


=COUNTIF(V4:V28,">"&NOW()+365)/COUNT(V4:V28)</PRE>
This is the conditional formating I put in $V$29

 

[cmlloveless]

The idea behind this formula is what I am looking for:

=COUNTIF(V4:V28>0,V4:V28+365< TODAY())/COUNT(V4:V28)

The first part of the formula "(V4:V28>0,V4:V28+365< TODAY())" is the formula that changes my cells red, but includes the all the cells I am using in the V column.

So what I want is to calculate all of the cells that are red in a column, meaning the date in the cell is over 365 days old, and divide that number by the total number of cells I am using in that column.