Excel - Conditional Format Left and Right Gradients Independently?

Question

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

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.

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.

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").

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.

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?

Yes, except for the fact that Type 2 has an extra option of "(none)" as shown in the first image in my post, but why would that matter for answering this question? — ImaginaryHuman072889
I am afraid this is not possible without VBA. It isn't possible to create a rule for the format itself, such as If(B2="Grass";"some color"). Because of this, and because you do have 18*18 possibilities, you will need this many conditional rules. If you can use VBA then this is probably pretty simple. I suggest a more simple solution and that would be using two cells for your result and applying regular and conditional formatting that would make those two cells appear as one. — Karl Kristjansson
Yes, I thought of using two separate cells side by side. I was trying to be fancy by combining them haha. Thanks for your help. — ImaginaryHuman072889
This can be accomplished by using VBA I'm sure, I have never tried but VBA should be able to look up in a separate table you would pre-create a RGB color for each value in your dropdown. You didn't specify VBA so I expected you did not want your users to receive a XSLM workbook. — Karl Kristjansson

E. Merckx E. Merckx · Accepted Answer · 2017-10-27T19:36:17

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 - Conditional Format Left and Right Gradients Independently?

1 Answers