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
  • 437
  • 2
  • 10
EA1234
  • 35
  • 1
  • 6
  • 1
    please share with us what have you achieved so far and where do you need help. Also please explain why do you need to do it with VBA instead of standard conditional formatting. – Máté Juhász Jun 14 '15 at 07:32
  • @MátéJuhász: I've updated the question with an example. Certainly a standard conditional formatting rule can accommodate the need here. But 2 reasons I want VBA: 1) If a user copies/pastes a cell instead of entering in the value, the conditional formatting will go away; 2) If it's possible I would like to know for knowledge. I really appreciate the help! – EA1234 Jun 14 '15 at 13:43
  • [this might be of use](http://superuser.com/questions/656864/excel-dual-graded-color-scales/692584#692584) – Raystafarian Jun 15 '15 at 17:12

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
  • 21,403
  • 6
  • 54
  • 73
  • When I added my code as instructed, this part of the code broke with error: ' 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 Do you know how to set this range into a single cell and allow Conditional Formatting to apply to a range of values (e.g. 1 thru 25)?? – EA1234 Jun 14 '15 at 21:35
  • Why do you want to autofill your range again and overwrite your existing data? I meant only the code about conditional formatting – Máté Juhász Jun 15 '15 at 04:18
  • Your range needs to be `target` and this needs to go in the worksheet module. – Raystafarian Jun 15 '15 at 09:23