vba 我无法清除和重置单元格的背景颜色
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14119936/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
I am not able to clear and reset the background color of a Cell
提问by CodeLover
With the help of the below command I am able to clear the contents of the cells but not their background color. How to clear and set the background color of cells in a range?
在以下命令的帮助下，我可以清除单元格的内容，但不能清除它们的背景颜色。如何清除和设置范围内单元格的背景颜色？
ob9.Range(ob9.Cells(1,StartCol),ob9.Cells(1,maxcolumn)).ClearContents
EDIT
编辑
I tried the below :
我尝试了以下：
CountFill = objExcel1.Application.WorksheetFunction.CountA(ob9.Rows(1))
CountBlnk = objExcel1.Application.WorksheetFunction.CountBlank(ob9.Rows(1))
TotalColumn= CountBlnk + CountFill
ob9.Range(ob9.Cells(1,CountFill + 1 ),ob9.Cells(1,TotalColumn)).Interior.ColorIndex(-4142) '= xlColorIndexNone
Can it be done in a single line?
可以在一行中完成吗？
Thanks
谢谢
回答by bonCodigo
Everything is fine. But don't selectgiven you are running a huge script (knowing what you went through so far)...
一切安好。但是不要选择，因为你正在运行一个巨大的脚本（知道你到目前为止经历了什么）......
with ob9.Range(ob9.Cells(1,StartCol),ob9.Cells(1,maxcolumn))
.Interior.ColorIndex = xlColorIndexNone
.Interior.ColorIndex = 120
End With
If you are directly using the rangeyou may even remove withblock, as it too has some performance-slowing drawback.
如果您直接使用 ，range您甚至可以删除with块，因为它也有一些降低性能的缺点。
Answer for your sub questions:
回答您的子问题：
How to get column name from column number?
How to set range based on the OP's
maxcolumnname or number.You mentioned you need
row 1,maxcolumnthen you can build the cell using those two data.MsgBox Sheets(3).Rows(1).Columns(5).Addressso try out:
MsgBox Sheets(3).Rows(1).Columns(maxcolumn).Address
如何从列号获取列名？
如何根据 OP 的
maxcolumn名称或编号设置范围。您提到您需要
row 1，maxcolumn然后您可以使用这两个数据构建单元格。MsgBox Sheets(3).Rows(1).Columns(5).Address所以试试：
MsgBox Sheets(3).Rows(1).Columns(maxcolumn).Address
回答by Richard
You could try
你可以试试
ob9.Range(ob9.Cells(1,StartCol),ob9.Cells(1,maxcolumn)).Select
Selection.Interior.ColorIndex = xlColorIndexNone
Selection.Interior.ColorIndex = xlNone
One of the last two lines should work, but I'm not sure off-handedly which one (I don't have Excel). If you could try both and report back, that would be great.
最后两行中的一行应该可以工作，但我不确定是哪一行（我没有 Excel）。如果您可以同时尝试并返回报告，那就太好了。
You can set colors using:
您可以使用以下方法设置颜色：
Selection.Interior.Color = RGB(255,0,0)
