Forum Discussion
CoreyWFG
Jun 05, 2023Copper Contributor
Bring cell colour with the result
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 t...
SnowMan55
Jun 07, 2023Bronze Contributor
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) )
CoreyWFG
Jun 12, 2023Copper Contributor
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.
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.