Excel-独立地设置条件格式左右梯度？ [英] Excel - Conditional Format Left and Right Gradients Independently?

问题描述

我正在处理有关神奇宝贝类型的Excel工作表。

I am working on an Excel sheet for Pokémon types.

工作表的一部分允许您选择神奇宝贝类型。 B3 和 C3 单元格允许通过数据验证从类型的下拉列表中选择类型。见下文。

One part of the sheet allows you to pick the Pokémon type. Cells B3 and C3 allow the type to be picked from a drop-down list of types via data validation. See below.

将这种类型组合成具有以下公式的单个单元格的表格（效果很好）：

There is another part of the sheet which combines this type into a single cell with the following formula (which works fine):

= IF(OR(C3=B3,C3="(none)"),B3,B3&"/"&C3)

在这种情况下，上面的公式将返回草/毒物。

In this case, the formula above would return Grass/Poison.

这一切都很好，但是我想使用此单元格并有条件地使用2色渐变对其进行格式设置以反映双重键入。

This is all well and good, but I want to take this cell and conditionally format it with a 2-color gradient to reflect the dual typing.

问题是我想不出办法为每个渐变颜色设置单独的条件格式设置规则。

The problem is that I can't figure out a way to have a separate conditional formatting rule for each gradient color.

换句话说，如果我有这两个条件格式设置规则（如下所示），它将应用一个或另一个，但不能同时应用。

In other words, if I have these two conditional formatting rules (shown below), it will apply one or the other, but not both.

一个明显的解决方案是使用单个条件格式设置规则来处理此特定组合。 （请参见下文。）虽然下面没有完整显示，但此规则的条件格式公式为 = AND（B3 = Grass，C3 = Poison）。

An obvious solution to this problem would be to have a single conditional formatting rule to handle this specific combination. (See below.) Although not fully shown below, the conditional formatting formula for this rule is =AND(B3="Grass",C3="Poison").

此解决方案确实有效。但是，考虑到有18种类型，这意味着我将不得不为此单元格制定18 ^ 2 = 324个独立的条件格式设置规则（以处理所有可能的类型组合），我怀疑Excel是否支持单个单元格的许多条件格式设置规则

This solution does work. However, considering there are 18 types, this means I would have to make 18^2 = 324 separate conditional formatting rules for this cell (to handle all possible type combinations) which, I doubt Excel supports that many conditional formatting rules for a single cell and even if it did, it would be way too tedious to integrate.

如果有办法让一个像元对一个像元应用两个渐变规则，那么，规则的数量仅为18 * 2 = 36（左渐变为18，右渐变为18），这是可以管理的。

If there is a way to have a cell apply two gradient rules to one cell, however, the number of rules would only be 18*2 = 36 (18 for the left gradients, and 18 for the right gradients), which is manageable.

有人知道如何是否有条件格式设置规则将渐变颜色仅应用于单元格的一半（并允许另一半单元格通过另一种带有渐变的条件格式设置规则来更改）？

Does anyone know how to have a conditional formatting rule apply a gradient color to only half of the cell (and allow the other half of the cell to be changed by another conditional formatting rule with a gradient)? Is this possible?

推荐答案

使用VBA添加条件格式怎么办？我没有听说过或没有经历过最多的条件格式设置规则，但是我并不声称自己是专家。

What about using VBA to add your conditional formatting? I have not heard or experienced a maximum of conditional formatting rules, but I don't claim to be an expert.

下面的代码将遍历每种类型并添加一个基于指定的颜色和类型指定的渐变条件格式规则。我发现对其进行了测试，并且对我有用。

The below code will loop through each type and add a conditional formatting rule for your gradient, based on a specified color and type designation. I spot tested it, and it worked for me.

Sub CondForm()

Dim colors() As Long
ReDim colors(1 To 18)
    colors(1) = RGB(255, 0, 0)
    colors(2) = RGB(255, 255, 0)
    '...Add other types here
    colors(18) = RGB(0, 0, 255)

Dim Types() As String
ReDim Types(1 To 18)
    Types(1) = "Fire"
    Types(2) = "Thunder"
    '...Add other types here
    Types(18) = "Water"

Dim Rng As Range
Set Rng = Selection

'Remove any previous formatting
Rng.FormatConditions.Delete

For i = 1 To 18
    'Add a rule for the type individually
    With Rng.FormatConditions.Add(Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=" & Chr(34) & Types(i) & Chr(34))
        .Interior.Pattern = xlPatternLinearGradient
        .Interior.Gradient.Degree = 0
        .Interior.Gradient.ColorStops.Clear
        .Interior.Gradient.ColorStops.Add(0).Color = colors(i)
        .Interior.Gradient.ColorStops.Add(1).Color = RGB(255, 255, 255)
    End With
    For j = 1 To 18
        'Add a rule for each combined type
        With Rng.FormatConditions.Add(Type:=xlCellValue, Operator:=xlEqual, _
            Formula1:="=" & Chr(34) & Types(i) & "/" & Types(j) & Chr(34))
            .Interior.Pattern = xlPatternLinearGradient
            .Interior.Gradient.Degree = 0
            .Interior.Gradient.ColorStops.Clear
            .Interior.Gradient.ColorStops.Add(0).Color = colors(i)
            .Interior.Gradient.ColorStops.Add(1).Color = colors(j)
        End With
    Next j
Next i


End Sub

让我知道是否可行对于您来说，我很想知道结果。

Let me know if this works for you, I'd be curious to hear about the results.

这篇关于Excel-独立地设置条件格式左右梯度？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！