Ok, I have a solution that works with a 3-color air conditioner. Basically you specify the region for my code. He then creates two ranges, one of negative numbers and one of positive ones. Then conditional formatting is applied
red-low yellow-medium-green-high to the positive range and
red-high yellow-medium-green-low to the negative range.
This was a quick fix, so it is careless and not reliable (for example, it only works in AZ columns due to lazy ASCII conversion for column numbers), but it works. (I would post a photo, but I do not have enough points)
---------------------edit---------------------------- ---
@pnuts is right, unless the data is symmetrical, this solution will not work as is. With this in mind, I came up with a new solution. First I will explain the general idea, and then just give the code, if you understand the logic, the code should be clear enough. This is a rather complicated solution for such a seemingly simple problem, but isn’t that always the case ?: -P
We still use the basic idea of the source code, create a negative range and apply a color scale to it, then create a positive range and apply an inverted color scale to it. As seen below
Negative ........... 0 ................ positive
green yellow red | red yellow green
So with our distorted data data_set = {-1, -1, -2, -2, -2, -2, -3, -4, 1,5,8,13} what I do is reflect the extreme value . In this case, 13, so now data_set = {-1 3, -1, -1, -2, -2, -2, -2, -3, -4, 1,5,8,13} Pay attention to additional element -1 3 . I assume that you have a button to enable this macro, so I save the extra -1 3 in the cell located below the button, so even if it’s not there, it is not visible (yes, I know that they can move the button and etc., but it was the easiest thing I could think of)
It’s good that everything is good and the green color corresponds to 13 AND -1 3, but the color gradient is based on percentiles (in fact, the color barcode uses the 50th percentile to determine the midpoint, or in our case, where the yellow section)
Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
also with our distribution {-1 3, -1, -1, -2, -2, -2, -2, -3, -4, 1,5,8, 13} we can start to see yellow in positive range around the number 8.5, since 8.5 is the 50th percentile. but in the negative range (even if we add a mirror of -1 3), the 50th percentile will be -2, so our yellow in the negative range will start at 2 !! Hardly an ideal. just as pnuts was mentioned, but we're getting closer. if you have enough symmetrical data, this will not be a problem, but again we will consider the worst case with skewed data sets
Then I statistically matched the midpoints .... or at least their colors. Since our extreme value (13) is in the positive range, we leave yellow at the 50th percentile and try to reflect it in the negative range by changing which percentile appears yellow (if the negative range had an extreme value, we would leave yellow in this 50th percentile and try to reflect it in the positive range). This means that in our negative range, we want to shift our yellow (50th percentile) from -2 to a number around -8.5 so that it matches the positive range. I wrote a function called Function iGetPercentileFromNumber(my_range As Range, num_to_find As Double) That's right! More specifically, it takes a range and reads the values into an array. Then he adds num_to_find to the array and finds out to which percentile num_to_find i nteger 0 -1 00 belongs (hence the i in the function name). Using the data from our example again, we will call something like
imidcolorpercentile = iGetPercentileFromNumber(negrange with extra element -13, -8.5)
Where -8.5 is negative (the 50th percentile of the positive range = 8.5). Do not worry, the code automatically provides ranges and numbers, this is just for your understanding. The function will add -8.5 to our array of negative values {-1 3, -1, -1, -2, -2, -2, -2, -3, -4, -8.5} then find out what percentile it is.
Now we take this percentile and pass it as the midpoint for our non-local conditional formatting. so we changed yellow from the 50th percentile
Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
to our new meaning
Selection.FormatConditions(1).ColorScaleCriteria(2).Value = imidcolorpercentile 'was 50
which was now painted with paints !! we basically created a color strip symmetrical in appearance. Even if our numbers are far from symmetrical.
Well, I know that it was a ton to read and digest. but here is the main takeaway of this code - it uses full 3-color conditional formatting (not just setting the two extreme colors the same to look like an abs value) - creates symmetric color ranges using a hatched cell (say, under the button) to store extreme values - uses statistical analysis to match color gradients even in distorted data sets
both steps are necessary, and not one of them is enough to create a true mirror color scale
Since this solution requires a statistical analysis of the data set, you will need to run it again every time you change the number (which actually happened earlier, I just never said)
and now the code. Put it in a VBA or other highlighting program. It's almost impossible to read as it is ... takes a deep breath
Sub main() Dim Rng As Range Dim Cell_under_button As String Set Rng = Range("A1:H10") 'change me!!!!!!! Cell_under_button = "A15" Call AbsoluteValColorBars(Rng, Cell_under_button) End Sub Function iGetPercentileFromNumber(my_range As Range, num_to_find As Double) If (my_range.Count <= 0) Then Exit Function End If Dim dval_arr() As Double 'this is one bigger than the range becasue we will add "num_to_find" to it ReDim dval_arr(my_range.Count + 1) Dim icurr_idx As Integer Dim ipos_num As Integer icurr_idx = 0 'creates array of all the numbers in your range For Each cell In my_range dval_arr(icurr_idx) = cell.Value icurr_idx = icurr_idx + 1 Next 'adds the number we are searching for to the array dval_arr(icurr_idx) = num_to_find 'sorts array in descending order dval_arr = BubbleSrt(dval_arr, False) 'if match_type is 0, MATCH finds an exact match ipos_exact = Application.Match(CLng(num_to_find), dval_arr, 0) 'there is a runtime error that can crop up when num_to_find isn't formated as long 'so we converted it, if it was a double we may not find an exact match so ipos_Exact 'may fail. now we have to find the closest numbers below or above clong(num_to_find) 'If match_type is -1, MATCH finds the value <= num_to_find ipos_small = Application.Match(CLng(num_to_find), dval_arr, -1) If (IsError(ipos_small)) Then Exit Function End If 'sorts array in ascending order dval_arr = BubbleSrt(dval_arr, True) 'now we find the index of our mid color point 'If match_type is 1, MATCH finds the value >= num_to_find ipos_large = Application.Match(CLng(num_to_find), dval_arr, 1) If (IsError(ipos_large)) Then Exit Function End If 'barring any crazy errors descending order = reverse order (ascending) so ipos_small = UBound(dval_arr) - ipos_small 'to minimize color error we pick the value closest to num_to_find If Not (IsError(ipos_exact)) Then 'barring any crazy errors descending order = reverse order (ascending) so 'since the index was WRT descending subtract that from the length to get ascending ipos_num = UBound(dval_arr) - ipos_exact Else If (Abs(dval_arr(ipos_large) - num_to_find) < Abs(dval_arr(ipos_small) - num_to_find)) Then ipos_num = ipos_large Else ipos_num = ipos_small End If End If 'gets the percentile as an integer value 0-100 iGetPercentileFromNumber = Round(CDbl(ipos_num) / my_range.Count * 100) End Function 'fairly well known algorithm does not need muxh explanation Public Function BubbleSrt(ArrayIn, Ascending As Boolean) Dim SrtTemp As Variant Dim i As Long Dim j As Long If Ascending = True Then For i = LBound(ArrayIn) To UBound(ArrayIn) For j = i + 1 To UBound(ArrayIn) If ArrayIn(i) > ArrayIn(j) Then SrtTemp = ArrayIn(j) ArrayIn(j) = ArrayIn(i) ArrayIn(i) = SrtTemp End If Next j Next i Else For i = LBound(ArrayIn) To UBound(ArrayIn) For j = i + 1 To UBound(ArrayIn) If ArrayIn(i) < ArrayIn(j) Then SrtTemp = ArrayIn(j) ArrayIn(j) = ArrayIn(i) ArrayIn(i) = SrtTemp End If Next j Next i End If BubbleSrt = ArrayIn End Function Sub AbsoluteValColorBars(Rng As Range, Cell_under_button As String) negrange = "" posrange = "" 'deletes existing rules Rng.FormatConditions.Delete 'makes a negative and positive range For Each cell In Rng If cell.Value < 0 Then ' im certain there is a better way to get the column character negrange = negrange & Chr(cell.Column + 64) & cell.Row & "," Else ' im certain there is a better way to get the column character posrange = posrange & Chr(cell.Column + 64) & cell.Row & "," End If Next cell 'removes trailing comma If Len(negrange) > 0 Then negrange = Left(negrange, Len(negrange) - 1) End If If Len(posrange) > 0 Then posrange = Left(posrange, Len(posrange) - 1) End If 'finds the data extrema most_pos = WorksheetFunction.Max(Range(posrange)) most_neg = WorksheetFunction.Min(Range(negrange)) 'initial values neg_range_percentile = 50 pos_range_percentile = 50 'if the negative range has the most extreme value If (most_pos + most_neg < 0) Then 'put the corresponding positive number in our obstructed cell Range(Cell_under_button).Value = -1 * most_neg 'and add it to the positive range, to reskew the data posrange = posrange & "," & Cell_under_button 'gets the 50th percentile number from neg range and tries to mirror it in pos range 'this should statistically skew the data the_num = WorksheetFunction.Percentile_Inc(Range(negrange), 0.5) pos_range_percentile = iGetPercentileFromNumber(Range(posrange), -1 * the_num) Else 'put the corresponding negative number in our obstructed cell Range(Cell_under_button).Value = -1 * most_pos 'and add it to the positive range, to reskew the data negrange = negrange & "," & Cell_under_button 'gets the 50th percentile number from pos range and tries to mirror it in neg range 'this should statistically skew the data the_num = WorksheetFunction.Percentile_Inc(Range(posrange), 0.5) neg_range_percentile = iGetPercentileFromNumber(Range(negrange), -1 * the_num) End If 'low red high green for positive range Call addColorBar(posrange, False, pos_range_percentile) 'high red low green for negative range Call addColorBar(negrange, True, neg_range_percentile) End Sub Sub addColorBar(my_range, binverted, imidcolorpercentile) If (binverted) Then 'ai -> array ints adcolor = Array(8109667, 8711167, 7039480) ' green , yellow , red Else adcolor = Array(7039480, 8711167, 8109667) ' red , yellow , greeb End If Range(my_range).Select 'these were just found using the record macro feature Selection.FormatConditions.AddColorScale ColorScaleType:=3 Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority 'assigns a color for the lowest values in the range Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _ xlConditionValueLowestValue With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor .Color = adcolor(0) .TintAndShade = 0 End With 'assigns color to... midpoint of range Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _ xlConditionValuePercentile Selection.FormatConditions(1).ColorScaleCriteria(2).Value = imidcolorpercentile 'originally 50 With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor .Color = adcolor(1) .TintAndShade = 0 End With 'assigns colors to highest values in the range Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _ xlConditionValueHighestValue With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor .Color = adcolor(2) .TintAndShade = 0 End With End Sub