Feb 24 2023 03:25 PM
Hi All,
I'm trying to build an Attendance list for a job site.
Employees will need to be scanned in and out each time they pass through the gate using a QR scanner which will list there name on a excel sheet column.
My idea is to run a sheet which if the name pops up once (odd) it turns green. But if the name shows up twice (even) then they both turn red. Same goes for if the employee comes and goes multiple times during the day. Showing green if scanned odd number of times in the column and showing red if scanned even times in the column and thus showing the employee has left.
Can I do this and if so how?
Much appreciated!
Feb 25 2023 02:51 AM
Let's say you want top apply this to B2:C1000.
Select this range. The active cell in the selection should be in row 2 (i.e. either B2 or C2).
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=AND($B2<>"", ISODD(COUNTIF($B$2:$B$1000, $B2)))
Click Format...
Activate the Fill tab.
Select green.
Click OK, then click OK again.
Repeat these steps, but with the formula
=AND($B2<>"", ISEVEN(COUNTIF($B$2:$B$1000, $B2)))
and red as color.
Feb 25 2023 02:36 PM
Thanks mate works great! Cheers.