Excel Databars fill height.

Question

Dear Community,

I have made a condition where these databars, are done in black, on a background fill color that changes depending on a certain number, hence the gradient.
But as You see, the black databar does not fill the full height of the bar. Which creates these disturbing small lines.
Anyone have a good suggestion for this?

(I already tried to fill them all with thick borders, didn't help)

Kind Regards

CG

Answer 1

Christoffer,

The only workaround I found is to draw a rectangle with a thick black frame and no fill over the cell.

.

Make a suggestion to Microsoft, a colored databar looks a nice and useful.
How do I give feedback on Microsoft Office? - Office Support

Andreas.

Sub Test()
Dim Ws As Worksheet
Dim R As Range
Dim Sh As Shape
Set Ws = ActiveSheet
#If Develop Then
'Delete all existing shapes
For Each Sh In Ws.Shapes
If Sh.Type <> msoComment Then Sh.Delete
Next
#End If
For Each R In Range("D1:E37")
Set Sh = Ws.Shapes.AddShape(msoShapeRectangle, R.Left, R.Top, R.Width, R.Height)
With Sh
.Fill.Visible = False 'No fill
With .Line
.Visible = True 'Add frame
.Weight = 3 'Thickness
.ForeColor.RGB = 0 'Color
End With
End With
Next
End Sub

Answer 2

Dear Andreas,

I've attached the file here:

https://lunduniversityo365-my.sharepoint.com/:x:/g/personal/ch0161gr-s_lu_se/EWeAzw6S7oBAsfDd1JUqL3sBYu0vBGU4rmhCkU0NATbcag?e=qeFfEn https://lunduniversityo365-my.sharepoint.com/:x:/g/personal/ch0161gr-s_lu_se/EWeAzw6S7oBAsfDd1JUqL3sBp4ofUE_K1BqV-37sXq2aHw?e=4526GcKindKind regards

Christoffer

Answer 3

Thanks for your time Andreas!

Yes, there definitely should be a way to have color based databars depending on values etc. Will make a suggestion, since now I just created a "hacky" way of achieving it.

In the meantime, that really looked good when the lines went away. How exactly did you manage to do it by drawing a rectangle?
I'm not that skilled in Excel, is the rectangle a free hand drawing or is it bound by the cell, is it time consuming if you have 100s of cells?
I'm a novice in these areas :)

I see now you added a script, will see if I understand how to use it!

Cheers

Christoffer

Answer 4

We need to see this or an example file that shows the issue.

Andreas.