Excel's Conditional Formatting to Only Part of a Cell

Question

Hi,

I am attempting to apply formatting to a part of a cell, based on the value from another cell. See the image embedded below:

It seems the conditional formatting wants to colour the text of the whole cell, but I only want part of the text coloured. Here is my conditional formatting:

I tried putting [Blue] just before "T1135" but Excel moved [Blue] to before the @ symbol.

Maybe this is not possible? Does anyone have any suggestions?

Thanks!

Kristine

Answer 1

As you have found, conditional formatting applies to the entire cell. There is no way to make it format only part of a cell.

Do the cells in the Client column contain a formula? If so, you cannot do it another way either, for you cannot format part of the result of a formula differently from the rest.

You could do the following:

• C3:C6 should just contain the names of the clients, not formulas.
• Right-click the sheet tab.
• Select 'View Code' from the context menu.
• Copy the code listed below into the worksheet module.
• Switch back to Excel.
• Save the workbook as a macro-enabled workbook (.xlsm).
• Make sure that you allow macros when you open the workbook.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim s As String
    Dim v As String
    Dim rng As Range
    If Not Intersect(Range("B3:B6"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        s = Range("B2").Value
        s = Left(s, Len(s) - 1)
        For Each rng In Intersect(Range("B3:B6"), Target)
            v = rng.Offset(0, 1).Value
            If rng.Value = "Yes" Then
                If Right(v, Len(s)) <> s Then
                    rng.Offset(0, 1).Value = v & " " & s
                    rng.Offset(0, 1).Characters(Start:=Len(v) + 2, Length:=Len(s)).Font.Color = vbBlue
                End If
            ElseIf Right(v, Len(s)) = s Then
                rng.Offset(0, 1).Value = Left(v, Len(v) - Len(s) - 1)
            End If
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

Answer 2

Re: "I am attempting to apply formatting to a part of a cell, based on the value from another cell"

You can do that using VBA code to an entire selection.
(1 cell or 1,000 cells)

Adjust the value set for "numChars" as desired.

It specifies the number of characters on the right side of the cell to be colored.

You can easily specify the color (current is vbBlue) - see options at bottom.

'---

Sub ColorPartOfText()
'Nothing Left to Lose - May 2022
Dim Rng As Excel.Range, rCell As Excel.Range
Dim strText As String, lngStart As Long, numChars As Long

Set Rng = Excel.Selection.Cells
numChars = 4 '<<<Adjust

For Each rCell In Rng
strText = rCell.Text
lngStart = VBA.Len(strText) - (numChars - 1)

rCell.Characters(lngStart, numChars).Font.Color = vbBlue
Next 'rCell
End Sub
'---

Nothing Left to Lose https://1drv.ms/f/s!Au8Lyt79SOuhZw2MCH7_7MuLj04 (free excel programs)

Answer 3

Wow that's awesome... I've only done a very small amount of dabbling in the arena of visual basic but I will definitely give this a try.

A couple of questions:

1. Does the VB code work in the online version of Excel?
2. A number of people in my organization access will be accessing this workbook... will they need to 'update/refresh' the workbook each time they go into it, as a result of adding this bit of code?

Thanks, Hans!

Answer 4

1. No, VBA code does not work in Excel Online, nor in Excel for iOS or Android. Only in the desktop version of Excel for Windows and Mac.
2. No, the formatting will stay put until someone changes the Yes/No values in column B.

Answer 5

Thanks again, Hans... since the VBA code doesn't work online, I'm sad... I will need to seek another solution, but thank you so much for your efforts here.

Best,

Kristine