Plotting horizontal bars over range

[verter2k]

Hi fellow Excel professionals,

I searched the Web and the forum, but didn't find the solution for a problem very similar to one regularly and recently discussed. I have to draw dynamic bar chart colored red, amber, or green depending on whether the value crossed the limit. That is an easy part. I also need to show the range itself as a bar (I tried to put transparent grey bar over the first one, but without much success). Is this possible at all? The problem with the range is that it doesn't start from 0. All values are within 0-100. No need to have any labels, but I tried to show gridlines, and if I put white box to remove lower part of the range bar, gridlines there are removed, too. It is a kind of box plot but a bit different.

Any ideas? MS Excel 2016 Win/Mac - if there is a possibility to do this in VBA, just let me know! Appreciate any advice!

Kindest regards,
Paolo

 

[Worf]

Hi

If you want to fill a bar with the range image, this is the code:

Sub Export()
Dim ws As Worksheet, r As Range, p As Point, s$, co As ChartObject
Set ws = ActiveSheet
Set r = ws.[d80:j82]
r.CopyPicture xlScreen, xlPicture
Set co = ws.ChartObjects.Add(Left:=0, Top:=0, Width:=r.Width, Height:=r.Height)
co.Activate
s = "c:\users\public\rng.jpg"
With co.Chart
    .Paste
    .Export Filename:=s, Filtername:="JPG"
End With
co.Delete
Set p = ws.ChartObjects("Chart4").Chart.FullSeriesCollection(2).Points(6)
p.Format.Fill.UserPicture s
End Sub

 

[verter2k]

Thank you!
It's not exactly like this, I've eventually found a solution for a single colored bar chart, but still don't know how to do this for a conditionally colored bar chart, which I able to produce independently. The image of current design is here

https://pixhost.org/show/156/64375701_barchart.png

 

[Worf]

• Are you using this codeless technique? How did you implement the conditional formatting? https://peltiertech.com/conditional-formatting-of-excel-charts/

• Colouring can be easily done with VBA. Is the picture below your desired layout?

 

[verter2k]

This is very nice and almost like the one I need. A friend of mine solved the problem using several tables (1 for every bar) and putting #N/A instead of 0 to prevent from displaying when needed.