Excel 2007 vba if语句根据珍贵的单元格颜色填充单元格颜色
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16187574/
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
Excel 2007 vba if statement fill cell colour based on precious cell colour
提问by user1955214
please help me, i have no idea how to work with colours in vba coding. am gonna try and explain this as simple as i can.
请帮助我，我不知道如何在 vba 编码中使用颜色。我会尝试尽可能简单地解释这一点。
i have cells from D3 to AH564 that has no values in them and never will. if i change the colour in any of these cells to blue, it must change the cell that is 14 cells next to it(R1C14) to green then again 14 cells blue and again green. just three times. and if i change a cell to green do the same thing.
我有从 D3 到 AH564 的单元格，其中没有任何值，而且永远不会。如果我将这些单元格中的任何一个的颜色更改为蓝色，它必须将旁边 14 个单元格的单元格（R1C14）更改为绿色，然后将 14 个单元格更改为蓝色，然后再次更改为绿色。只是三遍。如果我将一个单元格更改为绿色，则执行相同的操作。
i have tried several codes found on the site and modified them to my needs but all of them uses values and i dont know where to even start with colours.
我已经尝试了在网站上找到的几个代码，并根据我的需要修改了它们，但它们都使用了值，我什至不知道从哪里开始使用颜色。
background colours are as follows: Blue (RGB) 0, 112, 192 Green (RGB) 146, 208, 80
背景颜色如下： 蓝色 (RGB) 0, 112, 192 绿色 (RGB) 146, 208, 80
i want the macro to change the 14th cell to the other colour 3 times.
我希望宏将第 14 个单元格更改为另一种颜色 3 次。
thanks for all the help.
感谢所有的帮助。
回答by Our Man in Bananas
You have to use the Interior property of the cell, and then:
您必须使用单元格的内部属性，然后：
You can use ColorIndex (to use one of the 56 "preset" colors in Excel):
您可以使用 ColorIndex（在 Excel 中使用 56 种“预设”颜色之一）：
ActiveCell.Interior.ColorIndex = 36
Or you can use Color:
或者您可以使用颜色：
Range("A1:A6").Interior.Color = RGB(0,112,192)
You can read up on this at Chip Pearson'sexcellent article on using colors in Excel VBA
您可以在Chip Pearson关于在 Excel VBA 中使用颜色的优秀文章中阅读此内容
回答by teylyn
You can have VBA code that gets triggered when a cell is changed.
您可以拥有在单元格更改时触发的 VBA 代码。
Formatting a cell to have a different color, though, does not trigger a change event, so just changing a cell fill will not fire up any macro.
但是，将单元格格式化为不同的颜色不会触发更改事件，因此仅更改单元格填充不会触发任何宏。
Instead of formatting a cell with a fill color, you could enter a number or text into the cell. You can use conditional formatting that will change the color of a cell with a value.
您可以在单元格中输入数字或文本，而不是使用填充颜色格式化单元格。您可以使用条件格式来更改带有值的单元格的颜色。
Entering a text or number into a cell willtrigger a change event, and that event can easily change the fill color of other cells.
在单元格中输入文本或数字将触发更改事件，该事件可以轻松更改其他单元格的填充颜色。
So, set up conditional formatting if a cell contains a value, say, 1. If it's a 1, format it blue with a blue background fill. This way, the number won't be visible.
因此，如果单元格包含值（例如 1），则设置条件格式。如果是 1，则将其格式化为蓝色并填充蓝色背景。这样，号码将不可见。
Then use this macro (right-click the Sheet tab, click "View Code" and paste the code into the code window)
然后使用这个宏（右击Sheet标签，点击“查看代码”，将代码粘贴到代码窗口中）
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D3:AH564")) Is Nothing Then
If Target = 1 Then
Target.Offset(0, 14).Interior.Color = RGB(146, 208, 80)
Target.Offset(0, 28).Interior.Color = RGB(0, 112, 192)
Target.Offset(0, 42).Interior.Color = RGB(146, 208, 80)
End If
End If
End Sub
It is not clear if the trigger cell can be all over the range or just in column D. The macro can be adjusted to accommodate whatever you define.
不清楚触发单元是可以在整个范围内还是仅在 D 列中。可以调整宏以适应您定义的任何内容。
Edit:
编辑：
If you copy and paste a value into multiple cells, then try this:
如果您将一个值复制并粘贴到多个单元格中，请尝试以下操作：
Option Explicit
选项显式
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
If Not Intersect(Target, Range("D3:AH564")) Is Nothing Then
If Target.Rows.Count > 0 Then
For Each cel In Target
If cel = 1 Then
cel.Offset(0, 14).Interior.Color = RGB(146, 208, 80)
cel.Offset(0, 28).Interior.Color = RGB(0, 112, 192)
cel.Offset(0, 42).Interior.Color = RGB(146, 208, 80)
End If
Next cel
End If
End If
End Sub
