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 |