Calculating percentage based on cell color?

C

cmlloveless

New Member
Joined
Jun 29, 2011
Messages
24
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.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
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.

Code: 
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) & "%")
 
Last edited:
Upvote 0
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!
 
Upvote 0
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())
 
Upvote 0
Ruddles said:
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!
Click to expand...

When I was writing the code, I knew it cannot be as easy as I thought it is.
Thanks for explaining it so clearly!
 
Upvote 0
beedistinct said:
When I was writing the code, I knew it cannot be as easy as I thought it is.
Click to expand...
It's one of those things which you either know, or you spend hours trying to figure out why your code doesn't work!
 
Upvote 0
cmlloveless said:
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.
Click to expand...
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:-
Code: 
=COUNTIF(R1:R20,">"&NOW())

How many are within 30 days of today's date:-
Code: 
=COUNTIF(R1:R20,">"&NOW()+30)

Percentage of dates which have expired:-
Code: 
=COUNTIF(R1:R20,">"&NOW())/COUNT(R1:R20)

Percentage of dates which have expired or will expire within 30 days:-
Code: 
=COUNTIF(R1:R20,">"&NOW()+30)/COUNT(R1:R20)

Percentage of dates which haven't yet expired but will expire within 30 days:-
Code: 
=(COUNTIF(R1:R20,">"&NOW())-COUNTIF(R1:R20,">"&NOW()+30))/COUNT(R1:R20)
 
Upvote 0
Ruddles said:
Maybe you can adapt one or more of these to suit...

Percentage of dates which have expired or will expire within 30 days:-
Code: 
=COUNTIF(R1:R20,">"&NOW()+30)/COUNT(R1:R20)
Click to expand...

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?
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Filling cells partially (based on percentage) and any colour of my choice
Replies
7
Views
443
Peter_SSs
Peter_SSs
C
  • Question
Compare dates in two columns across several rows and count based on criteria.
Replies
2
Views
315
ccalkins
C
SaraWitch
  • Solved
Calculate percentage from sum in same cell
Replies
6
Views
443
duggie33
D
Rixn
  • Question
Conditional formatting cell color based on a number range.
Replies
4
Views
274
awoohaw
awoohaw
A
  • Question
VBA to change cell colour based on content
Replies
3
Views
145
Snakehips
Snakehips

Forum statistics

Threads
1,214,583
Messages
6,120,380
Members
448,955
Latest member
BatCoder

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top