# Conditional Formatting - Formula and VBA Solution

#### D_Ireri

##### New Member
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

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### Claus8528

##### New Member
Hi,

This will count the occurrences of a date given the month and the department (minus 1):
(Apply with Control+Shift+Enter)
Code:
``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

##### New Member
I used the staff instead of the department. The formula should be
Code:
``[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]``

Replies
0
Views
562
Replies
1
Views
1K
Replies
2
Views
213
Replies
3
Views
245
Replies
0
Views
669

1,195,633
Messages
6,010,815
Members
441,569
Latest member
PeggyLee

### 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.

### Which adblocker are you using?

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

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