在Excel中停止任意填充颜色更改或使用VBA应用条件格式

I have a spreadsheet that I applied the following conditional formatting to, to improve readability

我有一个电子表格,我应用了以下条件格式,以提高可读性

I found out however that I lose the colours every now and then - Excel changes RGB values of the fill colours to something completely different from what I originally used

然而我发现我偶尔会丢失颜色 - Excel会将填充颜色的RGB值更改为与我最初使用的颜色完全不同的颜色

To fix this (before I distribute the excel file) I am hoping to recreate the conditional formating above in VBA and attach it to many of the combo-boxes on the spreadsheet

为了解决这个问题(在我分发excel文件之前),我希望在VBA中重新创建上面的条件格式并将其附加到电子表格上的许多组合框中

The idea is as follows:

想法如下:

Check column C2:c100 , when the value

检查列C2:c100,当值

if cellvalue is 1,
- set rows in range I8:K800, M8:M800 to colour 2
- apply outline + outline colour to rows in range I8:K800, M8:M800

如果cellvalue为1,则设置范围I8中的行:K800,M8:M800到颜色2将轮廓+轮廓颜色应用于范围I8中的行:K800,M8:M800

if cellvalue is 2
- set rows in range I8:K800, M8:M800 to colour 2
- apply outline + outline colour to rows in range I8:K800, M8:M800

如果cellvalue是范围I8中的2个设置行:K800,M8:M800到颜色2将轮廓+轮廓颜色应用于范围I8中的行:K800,M8:M800

etc

I have this code

我有这个代码

Sub ColorCells()
Dim Data As Range
Dim cell As Range
Set currentsheet = ActiveWorkbook.Sheets("Sheet1")
Set Data = currentsheet.Range("C2:C200")

For Each cell In Data

If cell.Value = "1" Then
Range("A" & Data.Row, "H" & Data.Row).Interior.ColorIndex = 10

End If
Next

End Sub

I'm struggling with line 8; to apply the color/formatting to my desired range and will be happy for any help / someone can point out what I'm doing wrong

我在第8行挣扎;将颜色/格式应用到我想要的范围,并乐意为任何帮助/有人可以指出我做错了什么

I prefer to use conditional formating here so will be happier if anyone can reveal the reason for the colour-shifting.

我更喜欢在这里使用条件格式化,所以如果有人能够揭示变色的原因会更快乐。

2 个解决方案

#1

For the vba here Range("A" & Data.Row, "H" & Data.Row).Interior.ColorIndex = 10 you use Data.Row, but you should reference to cell.Row

对于vba这里Range(“A”&Data.Row,“H”和Data.Row).Interior.ColorIndex = 10你使用Data.Row,但你应该引用cell.Row

Range.Row returns the number of the first row. So in your code you only change the first row every time.

Range.Row返回第一行的编号。因此,在您的代码中,您每次只更改第一行。

Hope this helps.

希望这可以帮助。

Cheers

#2

One possible cause of your colors changing, is that the user(s) might be changing the Theme colors. Any colors assigned an ThemeColor are dynamic and responsive to changing Theme colors, for instance:

颜色变化的一个可能原因是用户可能正在更改主题颜色。分配到ThemeColor的任何颜色都是动态的,并且响应于更改主题颜色,例如:

It is possible to do VBA to create the format conditions, specifying the literal RGB values, which is probably preferably to your current approach, since it will still function as "conditional formatting".

可以通过VBA创建格式条件,指定文字RGB值,这可能最好是当前的方法,因为它仍然可以作为“条件格式”。

My approach would be to use the Macro Recorder, and record the actions of creating all of your formatting conditions. Then you can modify it in such a way to "hard-code" the color values.

我的方法是使用宏记录器,并记录创建所有格式条件的操作。然后,您可以通过“硬编码”颜色值的方式对其进行修改。

Here is a brief example; I recorded a macro and then modified the output code a bit to replicate your first format condition (I'm not 100% sure I have the same color, but this should give you the structure):

这是一个简短的例子;我记录了一个宏,然后稍微修改了输出代码以复制你的第一个格式条件(我不是100%确定我有相同的颜色,但这应该给你结构):

Sub ConditionalFormats()

    Dim rng As Range
    Dim cFormat As FormatCondition
    Set rng = Range("E8:G802")

    Set cFormat = rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=$C$8=4")
    cFormat.SetFirstPriority
    cFormat.StopIfTrue = False
    With cFormat.Interior
        .PatternColorIndex = xlAutomatic
        ' ### This was the original output using ThemeColor property
        ' .ThemeColor = xlThemeColorLight2
        ' ### Modify using Color property instead:
        .Color = 15849925
        ' ### TintAndShade is not needed if we're using the literal color value
        '.TintAndShade = 0.799981688894314
    End With

    '## To add another condition, redefine the rng and add a new condition as needed:
    Set rng = Range("I8:O802")
    Set cFormat = rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=$C$8=4")
    With cFormat.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 15849925
    End With
    '## Add code for borders, etc.
    With cFormat.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Color = vbBlack
        .Weight = xlThin
    End With
    With cFormat.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Color = vbBlack
        .Weight = xlThin
    End With
End Sub

Now, the formatting rules aren't dependent on the Theme colors, so even if user changes the Theme, the colors remain. (The pink cell above is not conditionally formatted, so it does change with the Theme, but the blue cells do not):

现在,格式规则不依赖于主题颜色,因此即使用户更改主题,颜色仍然存在。 (上面的粉红色单元格没有条件格式,所以它确实随主题变化,但蓝色单元格没有变化):

#1