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) )
alternatively you report that info in an adjacent cell or even in the same cell with the value. for example
= LET(vals, TOCOL( SHEET1:SHEET4!A1),
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:
