V******n
发帖数: 881 | 1 【 以下文字转载自 CS 讨论区 】
发信人: VAMclean (Mclean), 信区: CS
标 题: can anyone help me with a vba code? (you bao zi)
发信站: BBS 未名空间站 (Fri Jan 10 16:48:07 2014, 美东)
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 |
x*******a
发帖数: 11067 | 2 rng.Font.Color = rng.Interior.Color |
V******n
发帖数: 881 | 3 lao da
it needs to be in a loop and this one will create bugs...
【在 x*******a 的大作中提到】
: rng.Font.Color = rng.Interior.Color
|
x*******a
发帖数: 11067 | 4 你不就是想把那个区域的文字用背景颜色覆盖？这样跑不过去吗？不需要loop啊。
【在 V******n 的大作中提到】
: lao da
: it needs to be in a loop and this one will create bugs...
|
V******n
发帖数: 881 | 5 sorry i can't type chinese on this computer
background has conditional formatting created scaled colors, green in the
lower right corner and gradually moves to red in the upper left corner.
cell.Font.Color = cell.Interior.Color
the micro runs through but i can still see the numbers (font) after I
overwrote it using the interior color. somehow the font color only has 2
types, green or red, not scaled at all...
【在 x*******a 的大作中提到】
: 你不就是想把那个区域的文字用背景颜色覆盖？这样跑不过去吗？不需要loop啊。
|
n*****r
发帖数: 1087 | 6 Set rng = Range("v4:ak19")
For Each cell In Selection
我觉得是这两行有点问题，问题可能不在cell.Font.Color = cell.Interior.Color这
个loop里，可能你的selection不是rng这个range，compile时最好用step into一行一
行检查一下，容易发现哪里出了问题。
要不把第一行改改试试。
Range("v4:ak19").Select
For Each cell In Selection
如果说错了，当我没说。 |
n*****r
发帖数: 1087 | 7 应该跑不过去，每个cell的background color有自己的value，即使background color
都一样，应该还是跑步过去。
【在 x*******a 的大作中提到】
: 你不就是想把那个区域的文字用背景颜色覆盖？这样跑不过去吗？不需要loop啊。
|
V******n
发帖数: 881 | 8 我那个原始的code能跑过去
但font没覆盖住
而且我用cell.Font.Color=vbblue 试过也跑过去了，所有font都变成vbblue了
但是cell.Font.Color = cell.Interior.Color 就是盖不住
好像因为是conditional formatting，这个interior color不是渐进的color
【在 n*****r 的大作中提到】
: 应该跑不过去，每个cell的background color有自己的value，即使background color
: 都一样，应该还是跑步过去。
|
n*****r
发帖数: 1087 | 9 http://www.cpearson.com/excel/CFColors.htm
不知道这个link对你有没有用，cell.Font.Color=vbblue 行的话，那可能就是
Interior.Color 没能读出你想要的color value。这么说，如果background color是
conditional formatting，Interior.Color 这个写法是读不出来的。
Unfortunately, the Color and ColorIndex properties of a Range don't return
the color of a cell that is displayed if Conditional formatting is applied
to the cell. Nor does it allow you to determine whether a conditional
format is currently in effect for a cell. |
V******n
发帖数: 881 | 10 thanks, the functions didn't work
but I found an alternative way to resolve it. I hid the numbers...
already sent bao zi to the 2 friends above.
【在 n*****r 的大作中提到】
: http://www.cpearson.com/excel/CFColors.htm
: 不知道这个link对你有没有用，cell.Font.Color=vbblue 行的话，那可能就是
: Interior.Color 没能读出你想要的color value。这么说，如果background color是
: conditional formatting，Interior.Color 这个写法是读不出来的。
: Unfortunately, the Color and ColorIndex properties of a Range don't return
: the color of a cell that is displayed if Conditional formatting is applied
: to the cell. Nor does it allow you to determine whether a conditional
: format is currently in effect for a cell.
|
