StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Steps:- </p> <p>Step 1: Assign <code>Colors</code> array to Excel Cells. </p> <pre><code>yourRangeObject.Value = Colors; </code></pre> <p>Step 2: Write macro to color selected range of colors in a <em>System.String</em> </p> <pre><code> 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(); } </code></pre> <p>Step 3: Run macro written in Step 2</p> <pre><code>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); </code></pre> <p>Step 4: Assign <code>Values</code> array to Excel cells.</p> <pre><code>yourRangeObject.Value = Values; </code></pre> <p>Thats it...in two shots you can color code your excel cells.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload