none
How can I increment a selection range when applying a line border? RRS feed

  • Question

  • Hello. I have this code which I am trying to apply every 6 columns, how can I apply this every 6 columns?

                formatRange1 = oSheet.get_Range("B7", "B20");
                formatRange1.BorderAround(Excel.XlLineStyle.xlContinuous,
                Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic,
                Excel.XlColorIndex.xlColorIndexAutomatic);

    Normally I would use something like this:

                //Set misc label
                int rowsetmisc = 87;
                int columnsetmisc = 3;
    
                for (int i = 1; i <= 41; i++)
                {
                    oSheet.Cells[rowsetmisc, columnsetmisc].Value2 = "Misc: ";
                    columnsetmisc = columnsetmisc + 6;
                }
    However I cannot seem to get this to work with ranges and this line formatting.


    • Edited by _Sniffles_ Wednesday, August 1, 2018 3:05 PM
    Wednesday, August 1, 2018 3:02 PM

Answers

  • Hello _sniffles_,

    Please check if below code work for you.

     Microsoft.Office.Interop.Excel.Range range1271;
                for (int i = 1; i <= 41; i++)
                {
                    range1271 = oSheet.Range[oSheet.Cells[7,6 * i - 4], oSheet.Cells[20,6 * i - 4]];
                    range1271.BorderAround(Excel.XlLineStyle.xlContinuous,
                    Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic,
                    Excel.XlColorIndex.xlColorIndexAutomatic);
                }

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by _Sniffles_ Friday, August 3, 2018 4:39 AM
    Friday, August 3, 2018 1:34 AM

All replies

  •             //Apply border to lesson plan static weekday labels m-f
                int columnnumvar1 = 2;
                for (int i = 1; i <= 246; i++)
                {
                    Excel.Range testrange = ((Excel.Range)oSheet.Cells[6, columnnumvar1]);
                    testrange.BorderAround(Excel.XlLineStyle.xlContinuous,
                    Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic,
                    Excel.XlColorIndex.xlColorIndexAutomatic);
                    columnnumvar1 = columnnumvar1 + 1;
                }
    This code works for a single cell but so far cannot figure out how to make a multi-cell selection and apply it in this way.
    Wednesday, August 1, 2018 11:56 PM
  • Hello _sniffles_,

    What do you mean multi-cell selection? I did not see where you select any multi-cell range. 

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 2, 2018 1:48 AM
  • Hello and thank you.

    Yes, that is the problem. I do not know how I can select a range like B7-B20, create an outside line using the formatting above, add 6 columns, then add a line border to that selection. I cannot figure out how to increment the column in a way that uses the numbers that will let me apply that line style. : (

    I need a way to create some kind of loop to apply line formatting and add 6 columns. I need to do that 41 times. I can only get it to work if I use one cell like the code above.
    • Edited by _Sniffles_ Thursday, August 2, 2018 3:23 AM
    Thursday, August 2, 2018 3:19 AM
  • Hello _Sniffles_,

    What do you mean adding 6 column? Could you share some screenshots or detail range address to explain what you want to do?

    And your third part code is doing changes to 245 cells, I'm wondering what do you mean you could get it work for a single cell.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 2, 2018 7:03 AM
  • Sure thank you for trying so hard to help me out.

    I have a thick border around B7:B20.

    I need it to go around C7:C20, D7:D20 etc.

    I tried this:

                int columnnumvar1271 = 2;
                Excel.Range c1 = oSheet.Cells[7, columnnumvar1271];
                Excel.Range c2 = oSheet.Cells[20, columnnumvar1271];
                Microsoft.Office.Interop.Excel.Range range1271 = oSheet.get_Range("B7", "B20");
                for (int i = 1; i <= 41; i++)
                {
                    range1271 = oSheet.get_Range(oSheet.Range[c1, c2]);
                    range1271.BorderAround(Excel.XlLineStyle.xlContinuous,
                    Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic,
                    Excel.XlColorIndex.xlColorIndexAutomatic);
                    columnnumvar1271 = columnnumvar1271 + 6;
                }

    But it doesn't work. I get error message:

    System.Runtime.InteropServices.COMException
    HResult=0x800A03EC
    Message=Exception from HRESULT: 0x800A03EC

    Thursday, August 2, 2018 3:22 PM
  • Hello _sniffles_,

    Please check if below code work for you.

     Microsoft.Office.Interop.Excel.Range range1271;
                for (int i = 1; i <= 41; i++)
                {
                    range1271 = oSheet.Range[oSheet.Cells[7,6 * i - 4], oSheet.Cells[20,6 * i - 4]];
                    range1271.BorderAround(Excel.XlLineStyle.xlContinuous,
                    Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic,
                    Excel.XlColorIndex.xlColorIndexAutomatic);
                }

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by _Sniffles_ Friday, August 3, 2018 4:39 AM
    Friday, August 3, 2018 1:34 AM
  • Omg Terry, you are a genius! How did you figure this out?? Thank you so much!

    Is there any place you can recommend where I can learn more about C# and excel interop? : ( I would like to learn more but many places have outdated information or things that don't work, or very complex examples.

     
    Friday, August 3, 2018 4:38 AM