How to change color of interior and not borders

Question

I am trying to make an excel add-in with a button that tints all the cells except for the named range.

I am using the TintAndShade property, setting it to -0.7. The problem I have is that, when I open a new worksheet, the cells interior are in "No fill" mode. Therefore the TintAndShade property makes no difference.

Then, I tried to check if the Interior Color is by default (ColorIndex = -4142), and if it is the case, I set the color to White (ColorIndex = 2). The problem I have is that, it erases all the borders (I think the borders also are in "No Fill" mode). I can't set them to black because it is not the default color.

Do someone know how to change the Interior color without removing the default border color ? Or is there any other easier way to make such a button (that tints all the cells except for the named range) ?

Thank you !

Here is my code if you want to check :

int shadeMargin = 0;
Excel.Worksheet excelWorksheet = Globals.ThisAddIn.Application.ActiveSheet;
Excel.Names tags = Globals.ThisAddIn.Application.ActiveWorkbook.Names;

Excel.Range last = excelWorksheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Excel.Range xlRange = excelWorksheet.get_Range("A1", last);

 for (int i = 1; i < xlRange.Rows.Count + 1 + shadeMargin; i++)
 {
     for (int j = 1; j < xlRange.Columns.Count + 1 + shadeMargin; j++)
     {
         if (xlRange.Cells[i, j].Interior.ColorIndex == -4142) // Correspond to the "No Fill" color
         {
              xlRange.Cells[i, j].Interior.ColorIndex = 2; // Replaced by WHITE color
         }
         xlRange.Cells[i, j].Interior.TintAndShade = -0.7;

      }
 }

 foreach (Excel.Name tag in tags)
 {
     Excel.Range tagRange = tag.RefersToRange;
     for (int i = 1; i < tagRange.Rows.Count + 1; i++)
     {
         for (int j = 1; j < tagRange.Columns.Count + 1; j++)
         {
             tagRange.Cells[i, j].Interior.TintAndShade = 0;
         }
      }
  }

Do the cells actually have a border or are you seeing the Gridlines? You can go to View on the Ribbon and uncheck next to Gridlines in the "Show" group to verify. — Byron Wall
No you're right, they don't have a border when I uncheck the "Show" button. — Vilsisse

rgo rgo · Accepted Answer · 2015-04-11T02:53:07

The borders and shading are separate properties. In the absence of a fill color (e.g. white) the gridlines are shown. You can mimic gridlines by setting the Borders to match, but it will not be perfect. Use the macro recorder to get the codes for setting different borders linestyle, themecolor, tintandshade and weight, then experiment with setting them the way you want.

How to change color of interior and not borders

1 Answers