Conditional Formatting like Color Scale but doing it via Interior.Color

**Hyflex** · 02-04-2015, 05:44 AM

Hi,

I'm trying to add some static Interior.Color formats to cells but in the format of a color scale, the same as what conditional formatting would do but without the use of conditional formatting, by doing this it would make the cells 100% mobile compatible (compatible with Goggle sheets and other apps that disallow conditional formatting) aswell as making it faster, smaller (in file size) and won't change if you remove the any rows or change some values.

However, you could use it as a backup "underlay" where by a backup color scheme would be the static formatting under the conditional formatting which would only show on apps that disallow conditional formatting, so basically turning this into a fallback.

An example set of data to use is the following CSV data which will help explain what I am trying to achieve.

Data 1 (Yes there are blanks),Data 2,Data 3,Data 4,Data 5,Data 6
155.7321504,144.6395913,1,-4,-9.3844,0.255813953
113.0646481,120.1609771,5,-2,-2.5874,0.088082902
126.7759917,125.3691519,2,0,-0.0004,0.107843137
,0,7,,,0.035714286
123.0716084,118.0409686,4,0,0.3236,0.118881119
132.4137536,126.5740362,3,-2,-3.8814,0.090909091
70,105.9874422,6,-1,-0.3234,0.103896104

If you were to run the macro below would see the output I am wanting to achieve but the problem is this route is using conditional formatting, I am wanting to do it WITHOUT conditional formatting.

Sub con_formatting()

    ' Column A positive high, negative low
    Range("A2:A8").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 = 7039480
        .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 = 8109667
        .TintAndShade = 0
    End With
    
    ' Column B negative high, positive low
    Range("B2:B8").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
    
    ' Column C positive high, negative low
    Range("C2:C8").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 = 7039480
        .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 = 8109667
        .TintAndShade = 0
    End With
    
    ' Column D negative high, positive low
    Range("D2:D8").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
    
    ' Column E positive high, negative low
    Range("E2:E8").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 = 7039480
        .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 = 8109667
        .TintAndShade = 0
    End With
    
    ' Column F negative high, positive low
    Range("F2:F8").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
End Sub

After running it, you'd see the following:

Vqp6leQ.png

The colors that I would want the scale to be based on are:

GREEN: 99/190/123 (RGB) or 8109667 (Interior.Color)
YELLOW: 255/235/132 (RGB) or 8711167 (Interior.Color) (Mid-point)
RED: 248/105/107 (RGB) or 7039480 (Interior.Color)

I've found the following: http://www.cpearson.com/Excel/cformatting.htm but unfortunately it isn't compatible with Excel 2007 onwards (I am using Excel 2013), If the conditional formatting colors can be detected then a "hacky" workaround would suffice but it's very much possible (although I don't know how) to do it with a histogram.

This request could be very much an assest to other VBA and excel users as some people might not want colors to change if values are changed or rows are removed and also anyone opening sheets with this requested color scale formatting would be able to view the colors on/in services and apps like Google Sheets which strips out all conditional formatting and thus leaves the page completly blank which is far from ideal. I'm surpised Excel didn't include an easy "Convert to Static Formatting" or a fallback option like this for conditional formatting.

Hopefully someone can help and has an idea or method of doing this, it would be much appreciated.

See attached for example data + another description of how/what I want to achieve.

**Andy Pope** · 02-04-2015, 06:06 AM

this simple approach that will work with xl2010 onwards

Sub ApplyCF(Data As Range, HighColor As Long, MidColor As Long, LowColor As Long)

    With Data
        .FormatConditions.AddColorScale ColorScaleType:=3
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        .FormatConditions(1).ColorScaleCriteria(1).Type = xlConditionValueLowestValue
        With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
            .Color = HighColor
            .TintAndShade = 0
        End With
        .FormatConditions(1).ColorScaleCriteria(2).Type = xlConditionValuePercentile
        .FormatConditions(1).ColorScaleCriteria(2).Value = 50
        With .FormatConditions(1).ColorScaleCriteria(2).FormatColor
            .Color = MidColor
            .TintAndShade = 0
        End With
        .FormatConditions(1).ColorScaleCriteria(3).Type = xlConditionValueHighestValue
        With .FormatConditions(1).ColorScaleCriteria(3).FormatColor
            .Color = LowColor
            .TintAndShade = 0
        End With
    End With

End Sub
Public Sub MakeCFStatic(Data As Range)

    Dim Index As Long
    
    For Index = 1 To Data.Cells.Count
        Data.Cells(Index).Interior.Color = Data.Cells(Index).DisplayFormat.Interior.Color
    Next
    Data.Cells.FormatConditions.Delete
    
End Sub

Sub ShadeHighLow()

    Dim HighColor As Long
    Dim LowColor As Long
    Dim MidColor As Long
    
    
    HighColor = 7039480
    MidColor = 8711167
    LowColor = 8109667
    
    If TypeOf Selection Is Range Then
        ApplyCF Selection, HighColor, MidColor, LowColor
        MakeCFStatic Selection
    End If
    
End Sub
Sub ShadeLowHigh()

    Dim HighColor As Long
    Dim LowColor As Long
    Dim MidColor As Long
    
    HighColor = 8109667
    MidColor = 8711167
    LowColor = 7039480
    
    If TypeOf Selection Is Range Then
        ApplyCF Selection, HighColor, MidColor, LowColor
        MakeCFStatic Selection
    End If
    
End Sub

It uses conditional formatting to get the colour via the DisplayFormat object of the cell.

**Hyflex** · 02-04-2015, 06:36 AM

Oh wow, I didn't know about the "DisplayFormat" function, that is awesome!

Works perfectly, thank you. I'm impressed!

Conditional Formatting like Color Scale but doing it via Interior.Color

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Conditional Formatting like Color Scale but doing it via Interior.Color

Re: Conditional Formatting like Color Scale but doing it via Interior.Color

Re: Conditional Formatting like Color Scale but doing it via Interior.Color

Thread Information

Users Browsing this Thread

Similar Threads

Color Scale Conditional Formatting

Help with conditional formatting 3 color scale

[SOLVED] How To Check Interior Color Set By Conditional Formatting?

[SOLVED] Conditional formatting interior color from comparing two cell values VBA

Conditional Formatting 3 color scale

Bookmarks

Bookmarks

Posting Permissions