Excel VBA – Conductional Formating ( Databar )

Excel VBA – Conductional Formating ( Databar )

 
   Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
      Cells(1, 1).Value = "Product Name"
      Cells(1, 1).Font.Bold = True
 
      Cells(1, 2).Value = "Sales Amt"
      Cells(1, 2).Font.Bold = True
 
      For i = 2 To 21
         Range("A" + CStr(i)).Value = "Product " + CStr(i – 1)
         Range("B" + CStr(i)).Formula = "=FLOOR(RAND() * 10 + 1, 1)"
         Range("B22").Formula = "=SUM(B2:B21)"
         Range("C" + CStr(i)).Formula = "=B" + CStr(i) + "/B22"
      Next i
 
      Columns("A").EntireColumn.ColumnWidth = 13
 
      With Columns("B")
         .EntireColumn.ColumnWidth = 13
         .HorizontalAlignment = xlLeft
      End With
 
      With Columns("C")
         .EntireColumn.ColumnWidth = 15
         .NumberFormat = "0.00%"
      End With
 
      FillDataBars
 
   End Sub
 
   Sub FillDataBars()
 
      Dim rng As Range
      Set rng = Range("C2", "C21")
 
      rng.FormatConditions.Delete
 
      Dim db As Databar
      Set db = rng.FormatConditions.AddDatabar
 
      With db
 
         db.AxisPosition = xlDataBarAxisMidpoint
         db.ShowValue = True
         db.Direction = xlLTR
 
         db.BarFillType = xlDataBarFillGradient
 
         With db.BarColor
            .Color = vbBlue
            .TintAndShade = -0.4
         End With
 
         With db.BarBorder
            .Type = xlDataBarBorderNone
         End With
 
      End With
 
   End Sub