Calculating coloured/uncoloured cells in a table

vikosh

Board Regular
Joined
Dec 12, 2011
Messages
92
Office Version
  1. 2019
Platform
  1. MacOS
Hi,

And again, i need help from some bright "excellers".

This time, as per the attached screenshot, there is a table with 14 rows and 6 columns (A2:G17, A2 and A3 merged just to have the title "Name", then whole A4-A17 filled with names, B3-G3 filled with time slots).
14 rows corresponds to 14 users that enter their availability into 6 columns that represent time slots.
Users do that by choosing a value from the dropdown menu (there are two options "morning" which is being available for the defined time slot, "cours" which means unavailable or simply empty cell that also corresponds to not being available).
In total, i need to have 10 users to be available at any given time slot.
Meaning, every time slot column has to have at least 10 "green" cells.

If there are less than 10 "green" cells in any row, then merged cell "B2:G2" has to change background colour to red, otherwise, it changes back to no color.

I am having a problem with achieving that.

I basically need to have 10 users to cover all time slots.

The attached imager is an example of how users would fill that table up.

Please help. If possible without macros.

Thank you in advance.
 

Attachments

  • Screenshot 2024-11-06 at 15.20.44.png
    Screenshot 2024-11-06 at 15.20.44.png
    153.1 KB · Views: 4

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Forum statistics

Threads
1,223,842
Messages
6,174,981
Members
452,596
Latest member
Anabaric

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