• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how can we format the specific part of a sentence in a cell?

hoomantt

Member
with any possible application such as conditional formatting, Vb or...

like:

my car is blue.

"blue" be written in blue color.

"is" be written with underline.

"car" be written in bold.
 
Type your sentence in a cell. say My car is blue.

now select word "Blue" in formula bar and format it to blue from home menu, now select word car in formula bar and press Ctrl+B and so on...

its very easy and no need conditional formatting or VBA.
 
thanks.that is true.

now if my sentence comes from a formula example: concatenate("my car is blue"&$A$2) and i want to colorize word blue or bold it , what should we do?
 
Hi, hoomantt!

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.

Regards!
 
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?


Possible reading of interest (with VB)

http://www.mrexcel.com/forum/excel-questions/14459-color-part-cell.html
 
Oops, missed the part about it being generated by a formula. Good catch SirJB7. =/
 
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 ...
 
This can be helpful!

Extracted from- http://www.mrexcel.com/forum/excel-...plications-adjusting-color-adjacent-cell.html

Private Sub Worksheet_Change(ByVal Target As Range)
Set Crayon = Range("A1:BY21")
For Each 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
End If
If cell.Value = "Yellow" Then
cell.Interior.ColorIndex = 6
cell.Offset(0, 1).Interior.ColorIndex = 6
End If
If cell.Value = "Red" Then
cell.Interior.ColorIndex = 3
cell.Offset(0, 1).Interior.ColorIndex = 3
End If

'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

Next

End Sub


 
@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!
 
Back
Top