Excel radar log

[LarsP]

We're using an excel workbook to log operators entry and exits to different areas at work.
The log contains "Operator name" "Work area" "Date/Time of entry" "EntryChecklistComplete" "Date/Time of exit".

I want to make a row of ActiveX checkboxes on top of the sheet where i can mark a work area as "Safe", so when trying to log in an operator to a "unsafe" area OR if an area becomes "unsafe" while an operator is logged in the cell containing workarea changes color. For this to work I first need to be able to separate active and unactive areas, so if someone is logged in to an area end I uncheck the checkbox for that area the "area"cell for that area changes color. But if the operator is logged out (the Date/Time for exit") is filled in, the cell doesent change color.
I have worked how to check if an area is safe and chance cell color when someone is logged in, but having a hard time how to make ONLY the active lines (areas currently being worked in) change color if checkbox for area safe is unchecked, and also how to chance cellcolors back to white if an area becomes safe again when someone already is logged in. This is a logbook for a whole year at a time, getting 10k ish entrys pr year, so cant have it check up all cells containg "area1" each time the checkbox is unchecked or checked. Might not be the best formulated question, but hope someone can point me in the right direction

 

[NateSC]

Instead of Active X, for this case you can use the Form Controls Checkbox. After placing the control, right click it and select the Control tab. For the cell link, link it to the cell behind the control. Under the Colors and Lines tab, set the fill to White or whatever color you want. The fill will make it so you can't see the cell value changing when you click the box, but it will be True if checked and False if unchecked. Alternatively, you could have the linked cells somewhere far off to the right or even on another sheet and then hide the cells/sheet.

Now you can use conditional formatting on the Area cells. To do this I would use a separate conditional formatting for each area to keep it from getting confusing. You might be able to combine it with an IFS, but it made my head hurt to think through that. In the formatting, have it look at the Area column and if it says "area1" and the linked cell for the Area 1 check box is checked and decide if it needs to be shaded.

temp macro work.xlsm
	B	C	D	E
1
2	TRUE	FALSE	TRUE	FALSE
3		Area
4		area1
5		area2
6		area3
7		area1
8		area4
9		area1
10		area2
11		area4
12		area3
13		area1
14
Sheet8

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C4:C13	Expression	=IF(AND(C4="area1",NOT($B$2)),TRUE,FALSE)	text	NO
C4:C13	Expression	=IF(AND(C4="area2",NOT($C$2)),TRUE,FALSE)	text	NO
C4:C13	Expression	=IF(AND(C4="area3",NOT($D$2)),TRUE,FALSE)	text	NO
C4:C13	Expression	=IF(AND(C4="area4",NOT($E$2)),TRUE,FALSE)	text	NO