Board logo

標題: [發問] 儲存格範圍內底色巨集如何設定 [打印本頁]
作者: lp986749    時間: 2014-8-30 07:48     標題: 儲存格範圍內底色巨集如何設定

百分比
53.32%
88.65%
82.81%
66.80%
72.87%
83.08%
84.21%
86.65%
90.51%
97.10%
88.06%
86.88%
98.88%
89.46%
86.27%

範圍B3:B17百分比<70%底色改為黃色

範圍B3:B17百分比>90%底色改為綠色

範圍B3:B17百分比70%<X<90%底色無顏色

請問巨集如何寫?
作者: luhpro    時間: 2014-8-30 08:32

百分比
53.32%
...
86.8 ...
lp986749 發表於 2014-8-30 07:48
  1. Sub nn()
  2.   Dim rTar As Range
  3.   Dim rT
  4.   
  5.   Set rTar = Range([B3], [B17])
  6.   
  7.   For Each rT In rTar
  8.     With rT
  9.       Select Case .Value
  10.       
  11.       Case Is < 0.7
  12.         .Font.ColorIndex = 6
  13.         
  14.       Case Is > 0.9
  15.         .Font.ColorIndex = 43
  16.         
  17.       Case Else
  18.         .Font.ColorIndex = -4105
  19.         
  20.       End Select
  21.     End With
  22.   Next
  23. End Sub
複製代碼
作者: lp986749    時間: 2014-8-30 09:07

感激解惑此困擾問題我自己練習巨集如下
Sub Macro3()
'
' Macro3 Macro
'

'
    Range("B3:B17").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=0.9"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=0.7"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16752384
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13561798
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)