Forum Discussion
Count highlighted and text specific cells
There are three texts possible (OT, PT, ST), and highlighted when something is complete. Each patient will have at least one of the text above (already inputted as needed) and when the evaluation is complete then I manually change the background color to yellow. I am trying to automate a count of how many PT's are highlighted in yellow. I am familiar with counting text. I am somewhat familiar with counting highlighted cells. I cannot figure out how to do both simultaneously. Thanks in advance for your assistance.
1 Reply
I "borrowed" a user defined function from https://www.exceltip.com/tips/how-to-get-color-of-the-cell-using-vba-in-microsoft-excel-2010.html
It allows you to read out the background colour of a cell. On my Mac, yellow has the value of 6. I you can live with adding a (perhaps hidden) column that checks if the cell colour value is 6, then you can use a formula like:
=SUMPRODUCT(B1:B7*(A1:A7="PT"))..... to count the number PT with a yellow background, as demonstrated in the attached file.
