none
Set conditional formatting for duplicate values in excel column using c#

    Question

  • Please convert this to C#

    Range("B2:B9").Select
        Selection.FormatConditions.AddUniqueValues
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        Selection.FormatConditions(1).DupeUnique = xlDuplicate
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 13551615
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False

    I tried to convert this as:

       string sRange = sFactorNameCol + iStartRow.ToString() + ":" + sFactorNameCol + iEndRow.ToString();
       Excel.Range rng = wsiSheet.Range[sRange];

       rng.Select();
       rng.FormatConditions.AddUniqueValues();
       Excel.FormatCondition fCodn = rng.FormatConditions[rng.FormatConditions.Count];
       fCodn.SetFirstPriority();

       fCodn = rng.FormatConditions[1];
       //fCodn. = Excel.XlFormatConditionType.xlUniqueValues

       rng.Interior.Pattern = Excel.XlPattern.xlPatternAutomatic;
       rng.Interior.TintAndShade = 0;
       rng.Interior.Color = 13551615;

    Wednesday, October 24, 2012 10:02 AM

Answers

  • Hi Jay,

    Thanks for posting in the MSDN Forum.

    Would you please take a look at:

    
    
                Excel.Workbook xlWB = Application.ActiveWorkbook;
                Excel.Worksheet xlWS = xlWB.ActiveSheet;
                xlWS.Range["B2:B9"].Select();
                Excel.Range xlS = Application.Selection;
                xlS.FormatConditions.AddUniqueValues();
                var xlFC = xlS.FormatConditions[xlS.FormatConditions.Count];
                xlFC.SetFirstPriority();
                var xlFC1 = xlS.FormatConditions[1];
                xlFC1.Interior.Pattern = Excel.XlPattern.xlPatternAutomatic;
                xlFC1.Interior.TintAndShade = 0;
                xlFC1.Interior.Color = ColorTranslator.FromOle(13551615);

    I hope it can help you.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us


    Thursday, October 25, 2012 2:44 AM

All replies

  • Hi Jay,

    Thanks for posting in the MSDN Forum.

    Would you please take a look at:

    
    
                Excel.Workbook xlWB = Application.ActiveWorkbook;
                Excel.Worksheet xlWS = xlWB.ActiveSheet;
                xlWS.Range["B2:B9"].Select();
                Excel.Range xlS = Application.Selection;
                xlS.FormatConditions.AddUniqueValues();
                var xlFC = xlS.FormatConditions[xlS.FormatConditions.Count];
                xlFC.SetFirstPriority();
                var xlFC1 = xlS.FormatConditions[1];
                xlFC1.Interior.Pattern = Excel.XlPattern.xlPatternAutomatic;
                xlFC1.Interior.TintAndShade = 0;
                xlFC1.Interior.Color = ColorTranslator.FromOle(13551615);

    I hope it can help you.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us


    Thursday, October 25, 2012 2:44 AM
  • Hi Tom,

    Thanks for your reply. But the above code highlights the value which are unique and it works , but I need to highlight those cell who have duplicate values. Any help on that would be appreciated.

    TIA.

    Thursday, October 25, 2012 5:40 AM
  • Hi Jay,

      I need to highlight those cell who have duplicate values

    Would you please post a new thread? Let's continue discuss that goal in new thread.

    Thanks for your understanding.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, October 25, 2012 6:01 AM
  • Hi Tom, 

    Thanks for this code. I know this an old message but it still helps. Your code works fine for numbers but it does not, for strings in the same range.  I have tried it several times, but it has failed to identify duplicate strings in a column range.  Is it because the column has both strings and numbers or this is just a limitation of this function?  

    Thanks in advance.

    -Jessemb

    Thursday, September 12, 2013 2:24 PM