create horizontal bar from two values

[robertvdb]

I have a start value, and an end value, placed in two cells. Say value 5 in cell B2 and value 8 in cell B3. These values are subject to change.

Now I would like to create a yellow rectangle, WITHIN a larger orange rectangle. The orange rectangle has a fixed width (say 0 to 10).

The yellow rectangle starts at 5 and ends at 8. Or whatever values in B2/B3.

I'd imagine that can be done with some horizontal bar graph, but I can't figure it out.

Any help is appreciated. See enclosed image.

 

[Snakehips]

@robertvdb
I've struggled to figure this out using bar graph.
If you are ok to use vba then the below should do it.
Paste the code to the code pane of your relevant worksheet.
'Rectangle 1' is the Fixed rectangle. 'Rectangle 2' is the Variable Rectangle. Edit the names if required.
"Edit the number of units of fixed rectangle if not 10
Position your fixed rectangle as you need.
A and when the values in B2 or B2 change the code should then size and position the variable rectangle

Private Sub Worksheet_Change(ByVal Target As Range)
   If Intersect(Target, Range("B2:B3")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    Target.Value = UCase(Target.Value)
    Application.EnableEvents = True
  With ActiveSheet.Shapes.Range(Array("Rectangle 1"))
    W1 = .Width
    U1 = W1 / 10  '****Edit 10 if  rectangle 1 represents different number of units
    H1 = .Height
    L1 = .Left
    T1 = .Top
 End With

 With ActiveSheet.Shapes.Range(Array("Rectangle 2"))
    .Height = H1
    .Top = T1
    .Left = L1 + U1 * Range("B2")
    .Width = (Range("B3") - Range("B2")) * U1
 End With
  Application.EnableEvents = True
End Sub

HTH

 

[robertvdb]

Thanks Tony ! Works fine !

 

[Snakehips]

Robert, You are most welcome. Happy to help.
It just occurred to me that you might wish to prevent the entry of silly numbers eg negatives or too big.
If so then try something like the below.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Intersect(Target, Range("B2:B3")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
   If Not IsNumeric(Target) Then Exit Sub
   If Not Range("B3") >= Range("B2") Then Exit Sub
   
    Application.EnableEvents = False

  With ActiveSheet.Shapes.Range(Array("Rectangle 1"))
    W1 = .Width
    U1 = W1 / 10 '****Edit 10 if  rectangle 1 represents different number of units
    If Target < 0 Or Target > 10 Then Exit Sub  '****Edit 10 if  rectangle 1 represents different number of units
    
    H1 = .Height
    L1 = .Left
    T1 = .Top
 End With

 With ActiveSheet.Shapes.Range(Array("Rectangle 2"))
    .Height = H1
    .Top = T1
    .Left = L1 + U1 * Range("B2")
    .Width = (Range("B3") - Range("B2")) * U1
 End With
  Application.EnableEvents = True
End Sub

 

[robertvdb]

Thanks Tony !