Why formating don't occure after rng.Clear?

Question

I'm making small excel to reduce the amount of work at my job. I decided that because of frequent data refreshing using VB would be the best resolution to constant formatting and clearing cells. I haven't touch VB in 5 years and I don`t see why this isn't working.

The code should work I checked it as different buttons but after some copying and pasting, conditional formatting isn't working.

Set rng = Range("B1:B15")

    rng.Clear

    'Formating Data with color to Value
    Selection.FormatConditions.AddColorScale ColorScaleType:=3
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).ColorScaleCriteria(1).Type =   xlConditionValueLowestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 8109667
        .TintAndShade = 0
    End With

The code should empty cells B1 to B15 and format them to change colours based on value. Its only part of colour formatting I didn't want to put all 24 lines of code.

Why did you switch from `rng` to `Selection` midway through the code? Unless `Selection` happens to be `B1:B15`, this probably isn't what you intended. — John Coleman, May 11 '19 at 16:20
Yea i just saw that :D i was using macro maker to ease work Thank you :D — Arthaolis, May 11 '19 at 16:30

score 1 · Accepted Answer · answered May 11 '19 at 18:17

It's unclear from your questions, but there is subtle difference between Clear and ClearContents
- Range.Clear clears all formulas and formatting inside the Range object
- Range.CearContents clears the content of the cell (value) but leave formatting intact

So make sure you're using the right one, perhaps even both if necessary, also it's a good idea to avoid Select altogether. In your case Selectionis valid only if the user selects a specific Rangeinside the Worksheet.

It seems instead you might want to be applying the format to your rng object instead
```
rng.FormatConditions.AddColorScale ColorScaleType:=3 'and so on..
```

Why formating don't occure after rng.Clear?

1 Answers1