Color Part of Cell

[poikl]

I have a Vlookup formula (which you helped me build)that reviews a column of item #s and answers one of the following: Part approved;Product approved;Part approved and Product approved;Part rejected Product approved;Part special Product approved; etc many other compilations.
Now I would like that ONLY the word "rejcted" be Colorer RED,"special" Colored Blue, etc. is this possible??

 

[Jim North]

Yes, read the help text on conditional formating.

 

[BobUmlas]

No, FORMULAS cannot return results which are PARTLY color coded. Simple text entries can, or whole cells can, but not a single word or subset of characters.

 

[Jim North]

Bob is correct.

 

[Tom Urtis]

Yes it is possible to color the font of individual words or characters differently in the same cell. And yes it can be done via a formula, but with the formula acting as the trigger, as for instance in a worksheet calculation event. You need VBA to do it.

All that said, here is a way to do it using a worksheet change event, in a range of cells from A1:B10. Yes I know I just said it's possible with a calculation event, but because I don't know the range and all the possibilities of words and colors you have in mind, I wanted to at least show you that it is possible to achieve what you want. All we'd need to modify this for your situation is to loop through the range of cells housing your VLOOKUP formula, triggered by the Worksheet_Calculate event.

Because you apparently have a number of VLOOKUP return possibilities and goodness knows how many colors in mind, here is a Select Case structure to get you started. I have an ulterior motive by suggesting the _Change trigger - - if you can avoid the loops which I think would be needed to evaluate all the cells housing the formula, then maybe you'll consider having those determining values ("Part special Product approved", etc.) displayed by manual or VBA entry, not by calculation return. That way you can pinpoint the affected cell(s) and not loop unnecessarily through others.


Right click on your sheet tab, left click on View Code, and paste this into the sheet module.

By the way, if you are wondering why I included two cases (Case "" and Case Else) to exit the sub, it's just to save you the trouble of setting this up for you to enter your own code at either or both of those instances.

Anyway, here's the code to get you started.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("A1:B10")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
Target.Select
Select Case Target.Value

Case "Part approved"
'Enter code here depending on desired font color

Case "Product approved"
'Enter code here depending on desired font color

Case "Part approved and Product approved"
'Enter code here depending on desired font color

Case "Part rejected Product approved"
With ActiveCell
'Format all characters as red
.Font.ColorIndex = 3
'Format characters 1 thru 5 back to black
.Characters(1, ActiveCell.Characters.Count - 25).Font.ColorIndex = 1
'Reformat characters 14 thru 30 back to black
.Characters(14, ActiveCell.Characters.Count - 0).Font.ColorIndex = 1
End With

Case "Part special Product approved"
With ActiveCell
'Format all characters as blue
.Font.ColorIndex = 5
'Format characters 1 thru 5 back to black
.Characters(1, ActiveCell.Characters.Count - 24).Font.ColorIndex = 1
'Reformat characters 13 thru 29 back to black
.Characters(12, ActiveCell.Characters.Count - 0).Font.ColorIndex = 1
End With

Case ""
Exit Sub

Case Else
Exit Sub

End Select
End Sub

Hope this helps.

 

[Nancy]

Tom, my boss wants something like this for one of his reports. Can you explain how this works?
Thanks,
Nancy

 

[Tom Urtis]

Nancy --

How this works is, the VBA code is placed in a worksheet module, referencing the range of interest (in my example, A1:B10 but it could be any range, or just one cell). That range is where the values of interest ("Product approved", etc.) would be located. The question was how to color code individual words differently in the same cell.

What the code does is first shade all the characters the desired special color in that cell, such as red or blue, and then goes back and makes the non-desired characters (the ones except the magic word of "rejected" or whatever) shaded back to the color black.

If you notice, the code colors the character spaces, which hence shades the characters in those spaces. It is fairly predictable as to what character spaces to shade if you know what the possibilities of values are. The Case Select structure is useful to deal with each possible scenario. There are only a relative few possible values that are intended to occupy that range, according to how I interpreted the original post. So, the format code belonging to each Case depends on the value of that Case.

There are other ways to accomplish this formatting of individual characters, and other ways to trigger the format, not just when the cell changes as in this example, but when the worksheet is activated, or a calculation takes place on the sheet, or a cell is selected, or other events too.

Sorry for the long winded answer to a short question! My example may well not be the best approach to what your boss has in mind. If you want, post a question for your situation as a separate post and someone here will be able to help you.

 

[Juan Pablo González]

Tom, I believe that yuo can only change specific font properties for constant cells, not formulas.

With a cell like this

=IF(1,"Juan Pablo")

and this piece of code

Sub T()
With ActiveCell
.Font.ColorIndex = 3
.Characters(1, 2).Font.ColorIndex = 1
End With
End Sub

the color of "Juan Pablo" is always black... not just the first two characters. Did you do something else ?

 

[Tom Urtis]

Hi Juan,

One way is to use a combination Calculation and Change event to work around the “constant vs formula” format barrier, but I purposely didn’t post the code because it’s so impractical and would require too many If or Case structures, and probably looping, to accommodate the number of dependent formula cells’ possibilities of return values needing special formatting, and what formatting they should get. That was why I suggested to poikl that a Change event alone would be better.

To answer your question, here’s what I didn’t post because it has no practical value. It’s an ugly approach because it first changes the return value to text (which is the workaround), formats it, and when the precedent cell (B1 in this example) is changed, the formula gets re-entered in the A1 dependent cell. It only demonstrates how to use formulas through the Calculation event to format individual text characters, but it’s a lot more trouble than it’s worth:

Option Explicit
Private Sub Worksheet_Calculate()
Dim Parts As String
Parts = Sheet1.Range("A1").Value
If Parts = "Part rejected Product approved" Then
[A1] = [A1]
[A1].Activate
With ActiveCell
.Font.ColorIndex = 3
.Characters(1, ActiveCell.Characters.Count - 25).Font.ColorIndex = 1
.Characters(14, ActiveCell.Characters.Count - 0).Font.ColorIndex = 1
End With
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$1" Then Exit Sub
[A1].FormulaR1C1 = "=IF(RC[1]=1,""Part rejected Product approved"","""")"
End Sub

 

[Juan Pablo González]

Ah, I see what you did. In the calculate event change the formula to value, and color the text. Then, in the change event, put the formula back in there...

Well, have to say again, that's a lot more trouble than what you get for it, but thanks for explaining.