As far a I know you can apply color format to cells or parts of a cell if it content is a value (string or a number constants), but not to parts of a cell if it's a formula, just to the whole cell in this case.
Ditto to what SirJB7 said. Native XL doesn't support being able to format portions of the cell's contents based on conditions. That said, might be able to do some tricks with VB. Wouldn't be quite as smooth as CF, but might give a nice apperaance. Would VB be an option for us to consider?
Happened upon this thread while looking for a solution to apply conditional formatting to only one row in a cell ... and I hear you, Chandoo Ninjas - constants only (my environment prevents the use of code/vba).
So then, I wonder if one can take a cell that contains a formula that concatenates values from other sources/worksheets, convert *that* to a string to allow for conditional formatting ...
PrivateSub Worksheet_Change(ByVal Target As Range) Set Crayon = Range("A1:BY21") ForEach cell In Crayon
'Change formula cell color to match output value If cell.Value = "Green" Then
cell.Interior.ColorIndex = 4
cell.Offset(0, 1).Interior.ColorIndex = 4 EndIf If cell.Value = "Yellow" Then
cell.Interior.ColorIndex = 6
cell.Offset(0, 1).Interior.ColorIndex = 6 EndIf If cell.Value = "Red" Then
cell.Interior.ColorIndex = 3
cell.Offset(0, 1).Interior.ColorIndex = 3 EndIf
'Change adjacent cell color to match ouput value 'If Cell.Value = "Green" Then 'ActiveCell.Offset(0, 1).Interior.ColorIndex = 4 'End If
'Change adjacent cell color to match ouput value 2nd option 'If Cell.Value = "Green" Then 'Target.Offset(Crayon.Row - 1, Crayon.Column).Interior.ColorIndex = 4 'End If 'If Cell.Value = "Yellow" Then 'Target.Offset(Crayon.Row - 1, Crayon.Column).Interior.ColorIndex = 6 'End If 'If Cell.Value = "Red" Then 'Target.Offset(Crayon.Row - 1, Crayon.Column).Interior.ColorIndex = 3 'End If
@psynce
Hi!
You can build any formula that you want (there are a few restrictions that I can never remembet) and apply it's result to a CF case. If you're planning to do it in "that" way I suggest you to use a helper cell anywhere, build the formula retrieving values from wherever you need and just get sure that it returns a logical value (True or False), and then set your CF formula to "=Xn" unquoted, where Xn is the cell address of helper cell.
Is this what you're after or did I misread you?
Regards!