Formula to Count Highlighted Cells

[Yevette]

All,

I've seen many posts on this, but no exact / clear answer. Is there an Excel formula (not VBA) that can count conditionally formatted highlighted (yellow) cells? Or, is this not a function Excel can perform? I am using Excel 2007.

Thanks so much for your help!

 

[T. Valko]

All,

I've seen many posts on this, but no exact / clear answer. Is there an Excel formula (not VBA) that can count conditionally formatted highlighted (yellow) cells? Or, is this not a function Excel can perform? I am using Excel 2007.

Thanks so much for your help!

If the cells are conditionally formatted then you should be able to write a formula that uses the same logic as the CF rule to get a count.

You haven't provided any details so that's the best I can offer!

 

[Yevette]

Thank you for your reply,

I have certain cells (fields) in my spreadsheet that are (conditionally) formatted to highlight in yellow if the user fails to input data in the cell (mandatory fields). I am looking for a formula that will count the number of highlighted cells and if that number is greater than 0 that means the user did not complete form. I would put that formula in a cell and reference the cell in my VBA Code to give the User an error if they try to save the file without completing the mandatory fields. I have a working VBA code but now just need to be able to count the cells. Does that adequately clarify what I'm trying to do?

Thanks so much for your help.

 

[T. Valko]

Thank you for your reply,

I have certain cells (fields) in my spreadsheet that are (conditionally) formatted to highlight in yellow if the user fails to input data in the cell (mandatory fields). I am looking for a formula that will count the number of highlighted cells and if that number is greater than 0 that means the user did not complete form. I would put that formula in a cell and reference the cell in my VBA Code to give the User an error if they try to save the file without completing the mandatory fields. I have a working VBA code but now just need to be able to count the cells. Does that adequately clarify what I'm trying to do?

Thanks so much for your help.

Ok, so what's the CF rule you used to turn the cells yellow?

It sounds like you can just do a count of EMPTY cells?

 

[Yevette]

Cells are in range B122:F126. If cells in Col. B. are populated, so must the cells in C:F be populated. For example, AND(B122 <>"",C122="") ---> cell C122 is highlighted yellow.

 

[T. Valko]

Cells are in range B122:F126. If cells in Col. B. are populated, so must the cells in C:F be populated. For example, AND(B122 <>"",C122="") ---> cell C122 is highlighted yellow.

OK, I think this is what you're looking for:

=SUMPRODUCT((B122:B126<>"")*(C122:F126=""))

 

[Yevette]

Hmmm. That didn't work. To test it I ensured there were 2 highlighted cells. The result of the fomula is 0. Could that be because the cells also have data validation (all of the cells in that range have drop-down lists).

 

[T. Valko]

Hmmm. That didn't work. To test it I ensured there were 2 highlighted cells. The result of the fomula is 0. Could that be because the cells also have data validation (all of the cells in that range have drop-down lists).

What did you do to ensure there were 2 highlighted cells?

 

[Yevette]

Hi,

It appears I had an issue with Excel. It locked up and shut down on me so I just had to reboot. Once I rebooted and tried your formula again, it worked!

Thanks so much for your help! I really appreciate your time and patience!

 

[T. Valko]

Hi,

It appears I had an issue with Excel. It locked up and shut down on me so I just had to reboot. Once I rebooted and tried your formula again, it worked!

Thanks so much for your help! I really appreciate your time and patience!

You're welcome. Thanks for the feedback!