Bring cell colour with the result

Copper Contributor

I have a sheet with 4 tabs of data, I have set up a 5th tab to compare the first 4 tabs and take the lowest value and post it in the corresponding cell in tab 5 (which I already have completed) but then to also fill the colour of the tab it was taken from, as each tab has a colour assigned according to company it represents.  I am just trying to find lowest value and have it tell me where the lowest value came from.

4 Replies
I should mention that there can and will be some cells that have an equal value, so would there be a way to differentiate those as well? Like a result with 2 matching numbers fill with one colour and text with he other?


I am interpreting this request not as "a sheet with 4 tabs" (which does not make sense to me unless you have inserted one or more tab controls on a worksheet) but as "a workbook with 4 worksheets".


To my knowledge, a formula cannot determine the color of any worksheet's tab, but a VBA procedure can. With that in mind…


Part 1 - Without Tied Values


IF the tab colors are fixed (unchanging), you likely can accomplish this using just conditional formatting.  See the first four rows of the Comparisons worksheet in the attached workbook.  Comparison Values 1-4 demonstrate this with one set of 4 conditional formats. (Additional sets of 4 might be needed, depending on the relative positions of the cell with your comparison formula to the cells being compared.)

2023-06-07 CB 1.png


Part 2 - With Tied Values


Again, IF the tab colors are fixed, you could in principle accomplish this using conditional formatting. Unfortunately, to handle all possible 2-way ties, you would need a set of 10 conditional formats (4 for non-ties, and 6 more formats for all possible 2-way ties).


With yet more conditional formats (24 more per set?), you could even visually encode information about 3-way ties, perhaps with a patterned background.


Is this a good design? Definitely not for people with Color Vision Deficiency. And even people with normal color vision may quickly encounter text+background color combinations that are hard to read.


Alternative 1 – and this would handle the situation if tab colors are allowed to change – you could write a VBA procedure that accepts five cells as parameters, does the value comparisons, and assigns background and foreground colors as desired. (Recommendation: Make the output cell the first of the five parameters, and you can use a ParamArray for the remaining.) The tab color would be accessed via the <worksheet_object>.Tab.Color property.

Public Sub NumericMinimumAmongCompanies(ResultCell As Range _
        , ParamArray CompanyCells() As Variant)
'   This procedure compares the numeric values in each of the
'       four company-specific cells to determine those with the
'       minimum value.  That minimum value is assigned to the
'       result cell, and the cell's background color is changed
'       to the color of the tab of the worksheet that contains the
'       minimum value.
'   If there is a two-way tie for minimum value, the text color of
'       the result cell is changed to the color of the tab of the
'       second-encountered worksheet that contains the minimum value.

    Dim dblMinimumValue As Double
    Dim in2CellsWithMin As Integer
    Dim in4ParamIndex   As Long
    Dim rngCell     As Range    'one of the company cells
    Dim vntCellValue    As Variant
    Dim objCmpnyWksht   As Worksheet
    Application.EnableEvents = False
    '  --
    dblMinimumValue = 1.79769E+308  '(approx. the largest possible value)
    in2CellsWithMin = 0 '(to be explicit; variable initialization does this)
    '  --
    ResultCell.Value = Empty
    ResultCell.Interior.ColorIndex = -4142
    ResultCell.Font.ColorIndex = 1
    '----   Check the values in company-specific cells.
    Debug.Assert UBound(CompanyCells) = 3
    For in4ParamIndex = LBound(CompanyCells) To UBound(CompanyCells)
        Set rngCell = CompanyCells(in4ParamIndex)
        vntCellValue = rngCell.Value
        If VarType(vntCellValue) = vbDouble Then
            If vntCellValue < dblMinimumValue Then
                '  --   A new minimum was found.
                dblMinimumValue = vntCellValue
                in2CellsWithMin = 1
                '  --   (It may not be the most efficient code, but
                '       for simplicity...
                Set objCmpnyWksht = rngCell.Worksheet
                ResultCell.Value = dblMinimumValue
                ResultCell.Interior.Color = objCmpnyWksht.Tab.Color
                ResultCell.Font.ColorIndex = 1
            ElseIf vntCellValue = dblMinimumValue Then
                in2CellsWithMin = in2CellsWithMin + 1
                '  --   For a two-way tie:
                If in2CellsWithMin = 2 Then
                    Set objCmpnyWksht = rngCell.Worksheet
                    ResultCell.Font.Color = objCmpnyWksht.Tab.Color
                End If
                '  --   [Code for a three-way tie or four-way tie
                '       is left as an exercise for the reader.]
            End If
        End If
    Next in4ParamIndex
    Application.EnableEvents = True

End Sub


You would invoke this procedure from the (probably identical) Worksheet_Change event handler of each of the four company-specific worksheets.  E.g.:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngChgdCellsOfInterest  As Range
    Dim rngCell     As Range
    Dim strRow      As String
    Set rngChgdCellsOfInterest = Intersect(Target, Range("B:B"))
    If rngChgdCellsOfInterest Is Nothing Then
        GoTo WkshtChange_Exit
    End If
    For Each rngCell In rngChgdCellsOfInterest
        strRow = CStr(rngCell.Row)
        Call NumericMinimumAmongCompanies(Sheets("Comparisons").Range("B" & (5 + rngCell.Row)) _
                , Sheets("Co1").Range("B" & strRow) _
                , Sheets("Co2").Range("B" & strRow) _
                , Sheets("Co3").Range("B" & strRow) _
                , Sheets("Co4").Range("B" & strRow))
    Next rngCell
    Exit Sub

End Sub


Another possibility is to generate an Excel note for cells for which a tie is detected, listing the companies for which the values are tied.


Of course, using VBA means that your workbook becomes subject to the possible insertion of malware.


Alternative 2 – To reduce/avoid problems with colors, include textual information on the fifth (comparison) worksheet. A set of LET formulas can handle this, but additional visual space is required.

=LET( part1, IF('Co1'!B1=B11, ", Co1", ""),
    part2, IF('Co2'!B1=B11, ", Co2", ""),
    part3, IF('Co3'!B1=B11, ", Co3", ""),
    part4, IF('Co4'!B1=B11, ", Co4", ""),
    MID(part1 & part2 & part3 & part4, 3, 99) )


alternatively you report that info in an adjacent cell or even in the same cell with the value. for example
companies, {"abc"; "def"; "ghi"; "jkl"},
TEXTJOIN(" - ",, FILTER( companies, vals=MIN(vals) ), MIN(vals)))
I would further recommend making "companies" a Named variable or range and use short abbreviations like 1 or 2 letters for each company.
Lastly you can then apply conditional formatting on the sheet/range to highlight cells however you like based on those characters. I would first check if there is more than 1 "-" (i.e. a tie for min) and if not then if LEFT( ,2)=... then color... and if >1 "-" then some other color (you will need 1 rule for each color/condition)

I added a tab to the above example to show this alternative:



Sorry for improper terminology, I am a self-taught hack at all things computer, so am bound to mess up!
What I am doing is creating a workbook and entering hail insurance rate grids posted by 4 companies and wanting to find the lowest rate between all of them. I used this formula in the 5th tab (or sheet) to yank the lowest value =MIN('R&H:CAN'!B2) which works but I also want to take it a step further and have it colour the cell to correspond with the sheet from which it was taken; sheet 1 is orange, sheet 2 is blue, sheet 3 is yellow and sheet 4 is red. For the most part there likely isn't going to be many that match, but possibilty is definitely there, so a simple pattern would suffice to warrant manually checking into which sheets are the culprits.
The workbook is only used by myself and maybe one coworker as well for our own information (so no worry about vision issues or malware) to make the short sales window for this product as easy as possible each year; client calls and wants to know best rates and we have it at our fingertips within moments instead of combing through pages of rate tables for that client's area.