Color Excel Cells in one shot using two dimensional array

Question

Is there any way to assign two dimensional array of colors directly to excel cells?

In one shot we can assign cell values using Range.Value=Values[,], but I am not able to assign Colors[,] to these cells in one shot. Any kind of help will be appreciated.

Answer 1

Steps:-

Step 1: Assign Colors array to Excel Cells.

yourRangeObject.Value = Colors;

Step 2: Write macro to color selected range of colors in a System.String

 private static string GetMacro(int lastCellRowNum, int lastCellColNum)
    {
        StringBuilder sb = new StringBuilder();
        sb.Append("Sub FormatSheet()" + "\n");
        sb.Append("  Range(Cells(1, 1), Cells(" + lastCellRowNum + ", " + lastCellColNum + ")).Select " + "\n");
        sb.Append("  For Each c In Selection" + "\n");
        sb.Append("  c.Interior.Color = HEXCOL2RGB(c.Value)" + "\n");
        sb.Append("  c.Borders.Color = HEXCOL2RGB(\"#FFDEDDDD\")" + "\n");
        sb.Append("  Next" + "\n");
        sb.Append("  Selection.ClearContents" + "\n");
        sb.Append("End Sub" + "\n");

        sb.Append("Public Function HEXCOL2RGB(ByVal HexColor As String) As String" + "\n");
        sb.Append("  Dim Red As String, Green As String, Blue As String " + "\n");
        sb.Append("  HexColor = Replace(HexColor, \"#\", \"\")" + "\n");
        sb.Append("  Red = Val(\"&H\" & Mid(HexColor, 1, 2))" + "\n");
        sb.Append("  Green = Val(\"&H\" & Mid(HexColor, 3, 2))" + "\n");
        sb.Append("  Blue = Val(\"&H\" & Mid(HexColor, 5, 2))" + "\n");
        sb.Append("  HEXCOL2RGB = RGB(Red, Green, Blue)" + "\n");
        sb.Append("End Function");
        return sb.ToString();
    }

Step 3: Run macro written in Step 2

Microsoft.Vbe.Interop.VBComponent module = null;
module = workbook.VBProject.VBComponents.Add( Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule );
module.CodeModule.AddFromString(GetMacro(lastCellRowNum, lastCellColNum));
workbook.Application.Run("FormatSheet", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                                   Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                                   Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                                   Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

Step 4: Assign Values array to Excel cells.

yourRangeObject.Value = Values;

Thats it...in two shots you can color code your excel cells.