CODEWITHSUNDEEP
.netepplus
DK2014
DK2014

Reputation: 181

How to use Conditional Formatting to Highlight Duplicate Values in Excel using EPPLUS

I am trying to figure out how to highlight duplicate values in a given column using Conditional Formatting using EPPLUS excel library. In the list of validation types available, I cannot see Duplicate Value related type. The idea here is that a column has several values but some of those are duplicated. So I wanted to have a formatting applied similar to the manual steps i.e. select the column (or range in question), go to Conditional Formatting - Highlight Cell Rules - Duplicate Values. Then basically any duplicates to be assigned a light red fill color. Alternatively, if I can possibly use a new Rule Type with formula similar =COUNTIF($B3:$B$3, $B3)>1 which can be applied to the whole range. But how to add new Rule Type as Conditional formatting using EPPLUS? Any suggestions, ideas welcome. I haven't posted code as I haven't got around making anything that captures the logic.

Upvotes: 0

Views: 292

Answers (1)

DK2014
DK2014

Reputation: 181

after some trials and errors, managed to get this to work. sample code below hoping this can help someone looking for same info.

'Enable Automatic Calc mode on the excel workbook  
  p.Workbook.CalcMode = ExcelCalcMode.Automatic
 
 
 'Add Conditional formatting for the given range
 Dim cfRange As ExcelAddress = New ExcelAddress("B3:B" & ValToRow.ToString)
                Dim cfRule = ws.ConditionalFormatting.AddDuplicateValues(cfRange)
                cfRule.Style.Fill.PatternType = ExcelFillStyle.Solid

                cfRule.StopIfTrue = False

                cfRule.Style.Fill.BackgroundColor.Color = ColorTranslator.FromHtml("#F8696B") 'red color
                
               'The Pattern Type and color can be set or ignored as it may affect readability of cell values. 
               ' Uncomment to change and use as desired
                'cfRule.Style.Fill.PatternType = ExcelFillStyle.LightHorizontal
               'cfRule.Style.Fill.PatternColor.Color = Color.WhiteSmoke
                
                'Add this line after above code.
                ws.calculate()

Upvotes: 0

Related Questions