1

I'm trying to write VBA codes to conditionally format (2-color scale) cell A1 based on its value that between is >= cell A2 and <= cell A3. I'm using cells A2 and A3 so I can change the specific value range.

For example, if cell A2 is 50 and cell A3 is 1, change cell A1's background color from darkest green (highest; cell A2; 50) to lightest green (lowest; cell A3; 1) depending on A1's value - if not between 50 and 1, no format is necessary.

I was able to write the below code for a similar instance (in a module). The code below is for a given range of cells and what I want is for a single cell with changing values.

Sub ColorChange()

Application.ScreenUpdating = False

    ' Fill a range with numbers from 1 to 25.
    Dim rng As Range
    Set rng = Range("A1:A25")

    Range("A1") = 1
    Range("A2") = 2
    Range("A1:A2").AutoFill Destination:=rng

    rng.FormatConditions.Delete

    'Add a 2-color scale.
    Dim cs As ColorScale
    Set cs = rng.FormatConditions.AddColorScale(ColorScaleType:=2)

    ' Format the first color as light green
    With cs.ColorScaleCriteria(1)
        .Type = xlConditionValueLowestValue
        With .FormatColor
            .Color = vbGreen
            ' TintAndShade takes a value between -1 and 1.
            ' -1 is darkest, 1 is lightest.
            .TintAndShade = 0.5
        End With
    End With

    ' Format the second color as dark green, at the highest value.
    With cs.ColorScaleCriteria(2)
        .Type = xlConditionValueHighestValue
        With .FormatColor
            .Color = vbGreen
            .TintAndShade = -0.5
        End With
    End With

Application.ScreenUpdating = True

End Sub
Dirk Reichel
  • 447
EA1234
  • 35

1 Answers1

0

You need to use the "Worksheet_change" method to change the formatting when new data is added.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.FormatConditions.Count = 0 Then
        <add your code here>
    End If
End Sub

I don't have many experiences with conditional formatting in VBA. Maybe creating a new condition each time data is added will end up of a lot of formatting groups when you open Conditional formatting menu. To keep it as one formatting you may need to always set the formatting for your whole range (set rng=Range("A1:A" & target.row)).

VBA free solution: if you convert your data to a table (insert - table from EXCel 2007) than as far as users adds the lines immediately below existing data your formulas and conditional formattings will be automatically transferred to the new line too.

Máté Juhász
  • 22,071