I have not been able to find a way to do it as described above but I have found a way to achieve the same. Would still be interested to know if there is a direct solution that matches Excel's Duplicates conditioning via XlsIO if anyone knows?
For benefit of anyone else wishing to achieve this, here is my solution:
IConditionalFormats condition = MeasureSheet.Range["D2:D2000"].ConditionalFormats;
IConditionalFormat noDuplicates = condition.AddCondition();
noDuplicates.FormatType = ExcelCFType.Formula;
noDuplicates.FirstFormula = "countif(D:D,INDIRECT(ADDRESS(ROW(), COLUMN())))>1";
noDuplicates.BackColor = ExcelKnownColors.Red;
noDuplicates.FontColor = ExcelKnownColors.White;