conditional formatting only applies to part of cell text

  • Thread starter Thread starter klkropf
  • Start date Start date
K

klkropf

I only want the condtional formatting to apply to the part of the text that
equals "PCC". For example, the value of the cell may be, "Jane Smith - PCC"
and I only want PCC to be bold and green and Jane Smith to remain the same.
 
That would require VBA coding. Theres no way to do that with the normal
conditional formatting that excel gives you.
 
Do you know what the VBA code would be?

akphidelt said:
That would require VBA coding. Theres no way to do that with the normal
conditional formatting that excel gives you.
Click to expand...
 
to be honest I've never done this before so I really do not know. I imagine
you would have to loop through the range of cells everytime, search the cell
for the given word you want using StrComp or something, then some how select
the word within the sentence using some kind of find and range formulas and
then change the color to whatever you want.

It's possible but I'm not the man for the answer
 
Sub Bold_String()
Dim rng As Range
Dim Cell As Range
Dim start_str As Integer
Set rng = Selection
For Each Cell In rng
start_str = InStr(Cell.Value, "PCC")
If start_str Then
With Cell.Characters(start_str, 3).Font
.Bold = True
.ColorIndex = 10
End With
End If
Next
End Sub


Gord Dibben MS Excel MVP
 
Thanks, I can get it to work if PCC is actually typed in the cell, but I'm
using a Vlookup to return the value. Will it work when doing that?
 
Not unless you were to change the cell to a value.

You cannot format parts of formula results.


Gord
 
Gord:

Your previous post was very helpful! Is it possible to format part of a cell if the cell contains both values and formulas?

I'd like to format only the letter J in the following:

= ("J ") &TEXT(B18+TIME(0,6,0),"h:mm")

Thanks,
Jonathan



Gord Dibben wrote:

Not unless you were to change the cell to a value.
05-May-08

Not unless you were to change the cell to a value

You cannot format parts of formula results

Gor

wrote:

Previous Posts In This Thread:

conditional formatting only applies to part of cell text
I only want the condtional formatting to apply to the part of the text that
equals "PCC". For example, the value of the cell may be, "Jane Smith - PCC"
and I only want PCC to be bold and green and Jane Smith to remain the same.

That would require VBA coding.
That would require VBA coding. Theres no way to do that with the norma
conditional formatting that excel gives you

:

RE: conditional formatting only applies to part of cell text
Do you know what the VBA code would be

:

to be honest I've never done this before so I really do not know.
to be honest I've never done this before so I really do not know. I imagine
you would have to loop through the range of cells everytime, search the cell
for the given word you want using StrComp or something, then some how select
the word within the sentence using some kind of find and range formulas and
then change the color to whatever you want

It's possible but I'm not the man for the answe

:

Sub Bold_String()Dim rng As RangeDim Cell As RangeDim start_str As Integer
Sub Bold_String(
Dim rng As Rang
Dim Cell As Rang
Dim start_str As Intege
Set rng = Selectio
For Each Cell In rn
start_str = InStr(Cell.Value, "PCC"
If start_str The
With Cell.Characters(start_str, 3).Fon
.Bold = Tru
.ColorIndex = 1
End Wit
End I
Nex
End Su

Gord Dibben MS Excel MV

Re: conditional formatting only applies to part of cell text
Wow, I just tested that out... that is pretty sweet

:

Thanks, I can get it to work if PCC is actually typed in the cell, but I'm
Thanks, I can get it to work if PCC is actually typed in the cell, but I a
using a Vlookup to return the value. Will it work when doing that

:

Not unless you were to change the cell to a value.
Not unless you were to change the cell to a value

You cannot format parts of formula results

Gor

wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
ASP.NET MVC RC2
http://www.eggheadcafe.com/tutorials/aspnet/1a32540c-062b-49b8-8233-31135243bfdb/aspnet-mvc-rc2.aspx
 
Jonathan,

You cannot do that with a formula - but you can use event code to apply the format to a value.

HTH,
Bernie
MS Excel MVP
 
You would need to do this with VBA. What cell or cells have the formula you
are showing? What formatting do you want to do to the letter "J"? Can this
letter be more than one character?
 
Hi this expertise was very helpful. I don't know VB Script but I see the power. I would like to know how you would get the same result out of word in double quotes(a string for me because I am using Excel for some learning tools with programing.) I write code and variables in the cells. I was searching to find how I could format only specific words Like var and function in bold and colors. Boy you gave me a great answer. Now the question I have is how do you get a word in Excel(in a cell) any length wrapped in "" to do the same? I am going to experiment to see if I can figure it out. Thanks for you expertise.
 
Hi this expertise was very helpful. I don't know VB Script but I see the power. I would like to know how you would get the same result out of word in double quotes(a string for me because I am using Excel for some learning tools with programing.) I write code and variables in the cells. I was searching to find how I could format only specific words Like var and function in bold and colors. Boy you gave me a great answer. Now the question I have is how do you get a word in Excel(in a cell) any length wrapped in "" to do the same? I am going to experiment to see if I can figure it out. Thanks for you expertise.
 
Assumes only one word between quotes in any cell in the selected range..

Sub Between_Quotes()
Dim Test As String, First As Integer, Last As Integer, i As Integer, _
Length As Integer
Dim c As Range
For Each c In Selection
Test = c.Value
' find first double quote
For i = 1 To Len(Test)
If Mid(Test, i, 1) = """" Then
First = i + 1
Exit For
End If
Next

'find second double quote
For i = First + 1 To Len(Test)
If Mid(Test, i, 1) = """" Then
Last = i
Exit For
End If
Next

If Last = 0 Then Last = i
Length = Last - First
' now embolden and color from first to last characters
With c.Characters(Start:=First, Length:=Length).Font
.FontStyle = "Bold"
.ColorIndex = 3
End With
First = 0: Last = 0: Length = 0
Next c
End Sub


Gord Dibben MS Excel MVP
 
Back
Top