1

I'm trying to automate the creation of some pivot tables for a report that I need. I recorded a Macro to make the necessary changes, and it mostly functions correctly, apart from missing the conditional formatting. It works on the top cell of the selection, but that's all.

I'm aware that there has been issues with Macros in Excel and Conditional formatting, but I didn't find any answers to solve this problem. The section of the code that I believe applies to that condition is below.

    Range("G6:G13").Select
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
Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
    xlConditionValuePercentile
Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
    .Color = 8711167
    .TintAndShade = 0
End With
Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
    xlConditionValueHighestValue
With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
    .Color = 7039480
    .TintAndShade = 0
End With
Selection.FormatConditions(1).ScopeType = xlSelectionScope
Range("I6:I13").Select
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
Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
    xlConditionValuePercentile
Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
    .Color = 8711167
    .TintAndShade = 0
End With
Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
    xlConditionValueHighestValue
With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
    .Color = 7039480
    .TintAndShade = 0
End With
Selection.FormatConditions(1).ScopeType = xlSelectionScope

Current result I'm seeing: Current result I'm seeing

and the desired outcome

The Desired outcome

Thanks in advance for any help!

YowE3K
  • 23,852
  • 7
  • 26
  • 40
B Murphy
  • 11
  • 2
  • I would consider using a nested loop instead of the selection statements. If you are recording the macro I am assuming you are new to VBA, but you will find that selection statements are bad for and will cause a HUGE performance degradation on complex programs. That being said you can try here. http://stackoverflow.com/questions/13661965/conditional-formatting-using-excel-vba-code – Part_Time_Nerd Mar 31 '17 at 03:32

1 Answers1

0

Realise this is an old post and can't see any responses.

I had a similar problem, but when I stepped through the macro line by line I found that removing the last line Selection.FormatConditions(1).ScopeType = xlSelectionScope seemed to stop the problem.

I am unsure if there are unforeseen any knock on impacts of removing this yet, but will update if I find any.

Peter Csala
  • 17,736
  • 16
  • 35
  • 75
Nick
  • 1