How to color a half of an excel cell?

Question

Background: I need to color an excele cell in red or green color. If cell is more zero that I need to color the cell in green (in right way from the middle of the cell), if the cell is less zero I need to color the cell in red (in left way from the middle of the cell).

I use "Microsoft.Office.Interop.Excel" library.

How shall I do this?

P.S. Cell color changing In Excel using C# is not a duplicate, because of i want to color only half of an excel cell, not full.

you can do this via vba see here [VBA](https://newtonexcelbach.wordpress.com/2008/11/11/drawing-in-excel-7-creating-drawings-from-coordinates/) or [here](http://wellsr.com/vba/2015/excel/draw-lines-or-arrows-between-cells-with-vba/) — schoetbi, Aug 31 '16 at 08:05
Possible duplicate of [Cell color changing In Excel using C#](http://stackoverflow.com/questions/2452417/cell-color-changing-in-excel-using-c-sharp) — Martheen, Aug 31 '16 at 08:22
@Martheen I want color only half of an excel cell, not full. — Bushuev, Aug 31 '16 at 08:27

pnuts · Accepted Answer · 2016-08-31T09:22:21.573

3

This (a) may be cheating (b) perhaps better as a Comment (but then there would be no image) and (c) possibly stretching the significance of the [excel] tag here, but may be of some interest to mention that CF can achieve something of the sort:

ColumnB (red Fill) being formatted with a formula rule of:

=$B1<0

and ColumnC (green Fill) with a formula rule of:

=$B1>0

The cheating part is that B:C have been reduced in width and formatted Center Across Selection.

Something very vaguely similar with Sparklines:

In a Comment (with link to an image) @BrakNicku has pointed out that Data Bars could be applied (and the image rather proves that it is possible to half fill an Excel cell with colour). A variation, also Data Bars, is to have the length proportional to the underlying value:

edited Aug 31 '16 at 09:22

answered Aug 31 '16 at 08:39

pnuts

58,317
11
87
139

Can you say how to do it by using C# ? – Bushuev Aug 31 '16 at 08:41
2

It is also possible to mimic this formatting with CF data bars (both min and max set to 0) [sample](http://i.imgur.com/wGQFMlY.png) – BrakNicku Aug 31 '16 at 09:05
@h.o.m.a.n You can set DataBars with [EPPlus](http://blog.simontimms.com/2013/03/06/creating-databars-in-epplus/) – Martheen Aug 31 '16 at 09:11

Bushuev · Answer 2 · 2016-09-04T07:16:07.103

To solve this problem, I used the given scheme:

Create Macros in VBA (by using mouse).
Rewrite the macros to a general form.
Save the macros in C# application.
Save the macros in excel file(xlsm) by C#.
Run the macros from C#.

The given macro:

Sub CreateGistograms(r As String)
    Range(r).Select
    Selection.FormatConditions.AddDatabar
    Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .MinPoint.Modify newtype:=xlConditionValueAutomaticMin
        .MaxPoint.Modify newtype:=xlConditionValueAutomaticMax
    End With
    With Selection.FormatConditions(1).BarColor
        .Color = 8700771
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).BarFillType = xlDataBarFillGradient
        Selection.FormatConditions(1).Direction = xlContext
        Selection.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
        Selection.FormatConditions(1).BarBorder.Type = xlDataBarBorderSolid
        Selection.FormatConditions(1).NegativeBarFormat.BorderColorType = _
            xlDataBarColor
        With Selection.FormatConditions(1).BarBorder.Color
            .Color = 8700771
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
    With Selection.FormatConditions(1).AxisColor
        .Color = 0
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).NegativeBarFormat.Color
        .Color = 255
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).NegativeBarFormat.BorderColor
        .Color = 255
        .TintAndShade = 0
    End With
End Sub

How to save macro and run him from C# create macro at runtime in dotnet

@pnuts Based on you post (you provided wondefurl pictures) and the comment of BrakNicku, I decided to use vba macros to color a half of excel cell and run it via C#. — Bushuev, Sep 04 '16 at 08:03

How to color a half of an excel cell?

2 Answers2