Changing colour of cells based on odd or even times text is shows up in column

Copper Contributor

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. 

 

Liam Daines OutLiam Daines OutLiam Daines in, Darren Hille outLiam Daines in, Darren Hille out

 

 

Can I do this and if so how? 

 

Much appreciated! 

2 Replies

@ldaines23 

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.