none
How to select Excel Cell based on Font Color in the Given Range RRS feed

  • Question

  • Hi 

    I have implemented an Excel Add-in using VSTO and C#.

    I have to select one cell, based on the Font Color in the Given Range.

    Can anyone please help me out how to do this.

    Thanks

    Nihar

    Wednesday, July 26, 2017 10:27 AM

All replies

  • Hi NiharSai,

    You could iterate through cells in the given range and check the cells Font.Color Property. If you get the color you want, then you could select the cell and exit iterating.

    Here is the example.

               Excel.Application xlApp = Globals.ThisAddIn.Application;

                Excel.Worksheet xlWorksheet = xlApp.ActiveSheet;

                Excel.Range givenRange = xlWorksheet.Range["A1:D10"];

                foreach (Excel.Range cell in givenRange.Cells) {

                    if (cell.Font.Color == System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red)) {

                        cell.Select();

                        return;

                    }

                }

    Best Regards,

    Terry

    Thursday, July 27, 2017 7:23 AM
  • Hi Terry

    Thanks for the reply.

    your solution will work when we have few cells in the range.

    But what if it has more number of cells like 50k or so.

    then this, will be time consuming, right?

    But anyway, this will help me out.

    Thanks

    Friday, July 28, 2017 3:54 AM
  • Hi NiharSai,

    You could also use Find function to do this work. I have tested on 5000*100 cells and it could easily find the cell with red font. Here is the code example.

                Excel.Application xlApp = Globals.ThisAddIn.Application;

                Excel.Worksheet xlWorksheet = xlApp.ActiveSheet;

                Excel.Range givenRange = xlWorksheet.Range["A1:CV5000"];

                xlApp.FindFormat.Clear();

                xlApp.FindFormat.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);

                givenRange.Find(What:"*",SearchFormat:true).Select();

    Best Regards,

    Terry

    Friday, July 28, 2017 6:43 AM
  • Hi NiharSai,

    Has your original issue been resolved? If it has, I would suggest you mark the helpful reply or provide your solution and then mark it as answer to close this thread. 
    If not, please feel free to let us know your current issue.

    Best Regards,

    Terry

    Thursday, August 3, 2017 8:25 AM
  • Hi 

    Thanks for your Reply.

    your answer didn't worked for me.

    I used another approach to solve my issue.

    It worked.

    Friday, August 4, 2017 5:34 AM
  • Hi NiharSai,

    I'm glad to hear you have solved this issue. I suggest you share your solution and mark it answer to help other developers use this forum efficient. Thanks for understanding.

    Best Regards,

    Terry

    Friday, August 4, 2017 8:43 AM
  • Hi Terry

    our requirement was to select the values that are out of range

    in the Excel Range and then we need to check how many values are available.

    In those values, we need to select the smallest value.

    so what i implemented was iterating through each column and selecting the max values and checking if there is any other value smaller than the Maximum value. 

    If available will select the cell or else will select the Maximum value itself.

    In this way, i was able to find the solution for my issue.

    Thanks for your help Terry.

    Wednesday, August 9, 2017 11:13 AM
  • Hi NiharSai,

    Thanks for posting on MSDN.

    Since your original issue has been solved. I suggest you mark your reply to close this thread.

    For your new issue, I suggest you post a new thread so that other developers would be easier to find your issue and give you useful suggestion.

    Thanks for your understanding.

    Best Regards,

    Terry

    Thursday, August 10, 2017 10:26 AM