V******n
发帖数: 881 | 1 Sub Testing()
' Fill a range with numbers from 1 to 256.
Dim rng As Range
Set rng = Range("BF4:BF256")
Range("BF4") = 1
Range("BF5") = 2
Range("BF4:BF5").AutoFill Destination:=rng
' Add a 2-color scale.
Dim cs As ColorScale
Set cs = rng.FormatConditions.AddColorScale(ColorScaleType:=2)
' Format the first color as red
With cs.ColorScaleCriteria(1)
.Type = xlConditionValueLowestValue
With .FormatColor
.Color = vbRed
' TintAndShade takes a value between -1 and 1.
' -1 is darkest, 1 is lightest.
.TintAndShade = 0
End With
End With
' Format the second color as green, at the highest value.
With cs.ColorScaleCriteria(2)
.Type = xlConditionValueHighestValue
With .FormatColor
.Color = vbGreen
.TintAndShade = 0
End With
End With
' Try again with a rectangular range of values.
' Lowest values should be red, values at the 50th percentile
' should be red/green, high values are green.
Set rng = Range("v4", "ak19")
rng.FormatConditions.Delete
Set cs = rng.FormatConditions.AddColorScale(ColorScaleType:=3)
' Set the color of the lowest value, with a range up to
' the next scale criteria. The color should be red.
With cs.ColorScaleCriteria(1)
.Type = xlConditionValueLowestValue
With .FormatColor
.Color = vbRed
.TintAndShade = 0
End With
End With
' At the 50th percentile, the color should be yellow.
' Note that you cannot set the Value property for all
' values of Type.
With cs.ColorScaleCriteria(2)
.Type = xlConditionValuePercentile
.Value = 50
With .FormatColor
.Color = vbYellow
.TintAndShade = 0
End With
End With
' At the highest value, the color should be green.
With cs.ColorScaleCriteria(3)
.Type = xlConditionValueHighestValue
With .FormatColor
.Color = vbGreen
.TintAndShade = 0
End With
End With
' Add hide font color
Dim cell As Variant
'Dim rng As Range
Set rng = Range("v4:ak19")
For Each cell In Selection
cell.Font.Color = cell.Interior.Color
Next
End Sub
"
the last segment somehow doesn't work. please help! thanks and bao zi feng
shang!
Set rng = Range("v4:ak19")
For Each cell In Selection
cell.Font.Color = cell.Interior.Color
Next |
|
