Conditional Formatting - Formula and VBA Solution

[D_Ireri]

Hi

I have a vacation summary for various employees as show on the extract below. I would like a conditional format that highlights the dates taken by staff who are in a similar department and who have applied for vacation in the same month on the same dates. For instance, Staff A and Staff C are both in Department A and have both applied for vacation in the month of January on the same dates. The conditional formatting would then be applied to dates 7, 8 and 9 for both Staff A and Staff C.

On my spreadsheet, the data starts on row A1 and I am using Excel 2013. Please assist me with both a formula and a VBA solution.

Month	Staff Name	Department	Dates Taken	Dates Taken	Dates Taken	Dates Taken	Dates Taken	Dates Taken	Dates Taken	Dates Taken	Total Days Taken
January	Staff A	Department A	1	2	3	4	7	8	9		7
January	Staff B	Department B	1	2	7	8	9	10	11	12	8
January	Staff C	Department A	7	8	9						3
February	Staff D	Department A	4	8							2
February	Staff E	Department B	6	7	8						3
March	Staff F	Department A	14								1
May	Staff A	Department A	7								1
June	Staff F	Department D	8	9	10	11	12				5
July	Staff B	Department B	4	5	6	9	10				5

<tbody>
</tbody>

Thanks

David

 

[Claus8528]

Hi,

This will count the occurrences of a date given the month and the department (minus 1):
(Apply with Control+Shift+Enter)

IF(ISBLANK(D2),0,SUM(IF($A2=$A$2:$A$11,IF($B2=$B$2:$B$11,IF(D2=$D$2:$K$11,1,0),0),0))-1)

I don't know if you can use an array formula with conditional formatting but you could apply the formula to the right of your data and then use conditional formatting.

Hope it helps

 

[Claus8528]

I used the staff instead of the department. The formula should be

[COLOR=#333333]IF(ISBLANK(D2),0,SUM(IF($A2=$A$2:$A$11,IF($C2=$C$2:$C$11,IF(D2=$D$2:$K$11,1,0),0),0))-1)[/COLOR]