remaining quantity bar (related to total quantity)

torachan: thank you but i would prefer to avvoid macro.

leelnich: thank you. a quesion: the formula recall cell L4:

what is value in the cell L4? shouldn't i use only cell K6 and M6 (Q.ty total and remaining q.ty)?

Maybe i should use =LEFT(REPT("|",M6),M6*M6/K6)

but what is M6*M6/K6?


maybe is have to use
=LEFT(REPT("|",K6),M6) ?

Originally Posted by vermaccio

...thank you but i would prefer to avoid macro.

I've been working doubles for a couple of days, just got back on this last night. I see torachan beat me to it, and vermaccio's not interested, but here's my VBA code. This has 2 extra features; the bar responds to changes in EITHER column, and it handles multiple pasted entries.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MCL As Range, KCL As Range

With Range(Cells(6, "K"), Cells(Rows.Count, "K").End(xlUp))
    Set Target = Intersect(Target, .Offset(0, 2))
End With

If Not Target Is Nothing Then

For Each MCL In Target
    Set KCL = MCL.Offset(0, -2)
    With MCL.FormatConditions
        .Delete
    With .AddDatabar
        .MinPoint.Modify xlConditionValueNumber, 0
        .MaxPoint.Modify xlConditionValueFormula, "=" & KCL.Address
        .BarFillType = xlDataBarFillSolid
        
        If KCL > 0 Then
            .BarColor.Color = RGB(255, 85, 90)
        End If
        If KCL < MCL Then
            .BarColor.Color = RGB(255, 0, 0)
        End If
    End With
    End With
Next

End If

End Sub

Originally Posted by vermaccio

leelnich:... the formula recall cell L4:

what is value in the cell L4? shouldn't i use only cell K6 and M6 (Q.ty total and remaining q.ty)?...

maybe is have to use
=LEFT(REPT("|",K6),M6) ?

Not sure what you're referring to. I might have missed some worksheet "cleanup" after de-bugging. Sorry if that caused confusion.

Referencing THIS formula: =LEFT(REPT("|",23),23*M6/K6) , which appears in L6 and is copied down, by trial and error I found it took 23 pipes to stretch all the way across the cell. You may need to adjust that; use whatever fits. The important part is the ratio defined by M6/K6; it determines how many pipes (out of 23) are displayed.

I could have written it like this: =REPT("|",23*M6/K6), but that would return too many pipes for the cell if M6 inadvertently exceeds K6, which would alter your row height if you have selected Wrap Text for that cell.