Bring cell colour with the result

CoreyWFG 

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.)

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
    
WkshtChange_Exit:
    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) )