VBA：玩玩儲存格資料橫條顏色色階(AddDatabar)

老樣子來一下MSDN：FormatConditions.AddDataBar

圖1.資料橫條圖(三大法人買賣%)

這是小小編自己在看3大法人買賣強度的橫條圖，比例數字表示跟前一天相比的百分比；綠表買，紅表賣。

先從簡單的開始：

VBA：

做一個VBA ACTIVEX 命令按鈕然後貼下以上代碼。

	Private Sub CommandButton1_Click()
	sheet_name = ActiveSheet.Name
	Set rg = Sheets(sheet_name).Range("A" & 2)
	rg.FormatConditions.Delete
	rg.FormatConditions.AddDatabar
	End Sub

view raw AddDatabar_BAISC.VBA hosted with ❤ by GitHub

參考結果：

圖2.基本練習

先跑刪除再新增，相當簡單。

rg1.FormatConditions.Delete

rg1.FormatConditions.AddDatabar

進階應用：

VBA：

一樣做一個VBA ACTIVEX 命令按鈕然後貼下以上代碼。

	Private Sub CommandButton1_Click()
	sheet_name = ActiveSheet.Name
	END_FOR = Sheets(sheet_name).Range("a5000").End(xlUp).Row
	For I = 2 To END_FOR Step 1
	Set rg = Sheets(sheet_name).Range("A" & I & ":C" & I)
	Min = Application.Min(rg)
	Max = Application.Max(rg)
	For Each rg1 In rg
	If rg1.Value = 0 Then
	rg1.FormatConditions.Delete
	End If
	If rg1.Value > 0 Then
	rg1.FormatConditions.Delete
	rg1.FormatConditions.AddDatabar
	rg1.FormatConditions(rg1.FormatConditions.Count).ShowValue = True
	rg1.FormatConditions(rg1.FormatConditions.Count).SetFirstPriority
	With rg1.FormatConditions(1)
	.MinPoint.Modify newtype:=xlConditionValueNumber, newValue:=0 'xlConditionValueAutomaticMin
	.MaxPoint.Modify newtype:=xlConditionValueNumber, newValue:=Max * 1.5
	End With
	With rg1.FormatConditions(1).BarColor
	.Color = RGB(255, 77, 64)
	.TintAndShade = 0
	End With
	rg1.FormatConditions(1).BarFillType = xlDataBarFillGradient
	rg1.FormatConditions(1).Direction = xlContext
	rg1.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
	rg1.FormatConditions(1).BarBorder.Type = xlDataBarBorderSolid
	rg1.FormatConditions(1).NegativeBarFormat.BorderColorType = _
	xlDataBarColor
	With rg1.FormatConditions(1).BarBorder.Color
	.Color = RGB(255, 77, 64)
	.TintAndShade = 0
	End With
	rg1.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
	With rg1.FormatConditions(1).AxisColor
	.Color = 0
	.TintAndShade = 0
	End With
	With rg1.FormatConditions(1).NegativeBarFormat.Color
	.Color = 255
	.TintAndShade = 0
	End With
	With rg1.FormatConditions(1).NegativeBarFormat.BorderColor
	.Color = 255
	.TintAndShade = 0
	End With
	If rg1.Value >= 3 Then
	rg1.FormatConditions.Delete
	rg1.FormatConditions.AddDatabar
	rg1.FormatConditions(rg1.FormatConditions.Count).ShowValue = True
	rg1.FormatConditions(rg1.FormatConditions.Count).SetFirstPriority
	With rg1.FormatConditions(1)
	.MinPoint.Modify newtype:=xlConditionValueNumber, newValue:=0 'xlConditionValueAutomaticMin
	.MaxPoint.Modify newtype:=xlConditionValueNumber, newValue:=Max * 1.5
	End With
	With rg1.FormatConditions(1).BarColor
	.Color = RGB(255, 77, 64)
	.TintAndShade = 0
	End With
	rg1.FormatConditions(1).BarFillType = xlDataBarFillGradient
	rg1.FormatConditions(1).Direction = xlContext
	rg1.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
	rg1.FormatConditions(1).BarBorder.Type = xlDataBarBorderSolid
	rg1.FormatConditions(1).NegativeBarFormat.BorderColorType = _
	xlDataBarColor
	With rg1.FormatConditions(1).BarBorder.Color
	.Color = RGB(255, 77, 64)
	.TintAndShade = 0
	End With
	rg1.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
	With rg1.FormatConditions(1).AxisColor
	.Color = 0
	.TintAndShade = 0
	End With
	With rg1.FormatConditions(1).NegativeBarFormat.Color
	.Color = 255
	.TintAndShade = 0
	End With
	With rg1.FormatConditions(1).NegativeBarFormat.BorderColor
	.Color = 255
	.TintAndShade = 0
	End With
	End If
	End If
	If rg1.Value < 0 Then
	rg1.FormatConditions.Delete
	rg1.FormatConditions.AddDatabar
	rg1.FormatConditions(rg1.FormatConditions.Count).ShowValue = True
	rg1.FormatConditions(rg1.FormatConditions.Count).SetFirstPriority
	With rg1.FormatConditions(1)
	.MinPoint.Modify newtype:=xlConditionValueNumber, newValue:=0
	.MaxPoint.Modify newtype:=xlConditionValueNumber, newValue:=Min * 1.5
	End With
	With rg1.FormatConditions(1).BarColor
	.Color = RGB(54, 191, 54)
	.TintAndShade = 0
	End With
	rg1.FormatConditions(1).BarFillType = xlDataBarFillGradient
	rg1.FormatConditions(1).Direction = xlContext
	rg1.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
	rg1.FormatConditions(1).BarBorder.Type = xlDataBarBorderSolid
	rg1.FormatConditions(1).NegativeBarFormat.BorderColorType = _
	xlDataBarColor
	With rg1.FormatConditions(1).BarBorder.Color
	.Color = RGB(54, 191, 54)
	.TintAndShade = 0
	End With
	rg1.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
	With rg1.FormatConditions(1).AxisColor
	.Color = 0
	.TintAndShade = 0
	End With
	With rg1.FormatConditions(1).NegativeBarFormat.Color
	.Color = RGB(54, 191, 54)
	.TintAndShade = 0
	End With
	With rg1.FormatConditions(1).NegativeBarFormat.BorderColor
	.Color = RGB(54, 191, 54)
	.TintAndShade = 0
	End With
	End If
	Next
	Next I
	End Sub

view raw AddDatabar_ADVANCED.VBA hosted with ❤ by GitHub

參考圖3結果：多了顏色、最大最小值設定。

CODE相當多要設定，橫條圖效果才會顯著。

圖3.進階練習

下載