vba vba如何检查单元格是否没有格式或颜色
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17848218/
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
vba how to check if a cell has no formatting or color
提问by cheapkid1
I recently had a question regarding how to copy a cell value into all cells below it and stop based on when column A got to a blank cell. post
我最近有一个关于如何将单元格值复制到它下面的所有单元格并根据 A 列何时到达空白单元格停止的问题。 邮政
The excel sheet I'm working in has many divider rows that are color filled across the entire row to separate categories as you scroll down the sheet. I would like to be able to skip these separating rows in column A when the macro checks for a blank cell in column A. Or I would like to just assign StopRow to the first cell which has no formatting/no color/no value.
我正在使用的 Excel 工作表有许多分隔行，这些行在整行中填充颜色，以便在向下滚动工作表时分隔类别。当宏检查 A 列中的空白单元格时，我希望能够跳过 A 列中的这些分隔行。或者我只想将 StopRow 分配给第一个没有格式/没有颜色/没有值的单元格。
Here is what I have, thanks to Ripster earlier today, but I've failed incorporating a proper if then statement with what he came up with.
这就是我所拥有的，感谢今天早些时候的 Ripster，但我未能将正确的 if then 语句与他的想法结合起来。
Sub Example()
Dim MasterValue As String
Dim StopRow As Long
Dim i As Long
'Get the master value
MasterValue = Range("C5").Value
'Get the first blank cell in column A
StopRow = Range("A1").End(xlDown).Row
'Start at row 6 and continue to the "Stop Row"
For i = 6 To StopRow
'Set every cell from row 6 in column 3 to the "Master Value"
Cells(i, 3).Value = MasterValue
Next
End Sub
Please help.
请帮忙。
Thanks
谢谢
回答by lowak
This took me a while but I found solution to your problem ;)
这花了我一段时间，但我找到了解决您问题的方法；)
If macro goes to cell with different color - checking and do nothin, next "i" is taken. This should do what u want. It's possible to add more color ;)
如果宏转到具有不同颜色的单元格 - 检查并且不执行任何操作，则采用下一个“i”。这应该做你想做的。可以添加更多颜色;)
Link to colors - http://dmcritchie.mvps.org/excel/colors.htm
颜色链接 - http://dmcritchie.mvps.org/excel/colors.htm
Sub Example()
For i = 6 To 1200
If Cells(i, 3).Interior.ColorIndex = 1 Then 'here is color black
Else
If IsEmpty(Cells(i, 1)) = True Then 'if cells in column "A" is empty then stop
Exit For
Else
Cells(i, 3).Value = Range("C5").Value
End If
End If
Next i
End Sub
结束子
回答by Pane
Your conditions for StopRow aren't clear. Do you want to set StopRow when the cell has a conditional formatting rule or simply when it has a different format than the default ? A cell may have a rule but it may not be applied. Anyway, the function presented hereis something you might find of use.
您的 StopRow 条件不清楚。当单元格具有条件格式规则时还是只是在它具有与默认格式不同的格式时，您要设置 StopRow 吗？一个单元格可能有一个规则，但它可能不会被应用。无论如何，这里介绍的功能是您可能会发现使用的功能。
Copy the ActiveCondition function somewhere in a module and then change your for loop like so:
复制模块中某处的 ActiveCondition 函数，然后像这样更改 for 循环：
For i = 6 To StopRow
If ActiveCondition(Cells(i,3))=0 Then StopRow=i : Exit For
Cells(i, 3).Value = MasterValue
Next
If you want to check for change in font color that didn't come from conditional formatting then you'd need an extra line:
如果您想检查不是来自条件格式的字体颜色变化，那么您需要额外的一行：
For i = 6 To StopRow
If ActiveCondition(Cells(i,3))=0 Then StopRow=i : Exit For
If Cells(1,3).Font.ColorIndex=0 Then StopRow=i : Exit For
Cells(i, 3).Value = MasterValue
Next
There are more elegant ways to do this but this solution is the easiest to implement in your code.
有更优雅的方法可以做到这一点，但此解决方案最容易在您的代码中实现。
Hope this helps.
希望这可以帮助。
