Change only bullet font color

[ABabeNChrist]

I need a little assistance. I sometimes add a billet at the start of a sentence and I was trying to use conditional formatting so that ONLY the billet font would turn red when ever used for either each sheet or entire workbook. Any suggestions greatly appreciated. I know how to select the billet and change the color manually; I was just trying to find an automatic way

[HansV]

Do you mean bullet?

[HansV]

If you mean bullet: conditional formatting applies to a cell as a whole, not to individual characters in a cell.
It might be possible to use VBA code to format bullets, but that might not be desirable: VBA code tends to disable Excel's undo feature.

[ABabeNChrist]

Do you mean bullet?

Sorry, yes I meant bullet

[ABabeNChrist]

If you mean bullet: conditional formatting applies to a cell as a whole, not to individual characters in a cell.
It might be possible to use VBA code to format bullets, but that might not be desirable: VBA code tends to disable Excel's undo feature.

Thank you

[HansV]

Also, the code would only work for cells in which you enter a value, not for cells with a formula.
Would you like to have the VBA code, or will you work it our yourself?

[ABabeNChrist]

Also, the code would only work for cells in which you enter a value, not for cells with a formula.
Would you like to have the VBA code, or will you work it our yourself?

Yes coding sounds good as I do not use formula's, only VBA and conditional formatting

[HansV]

For one sheet:

Right-click the sheet tab.
Select View Code from the context menu.
Copy the following code into the worksheet module:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cel As Range
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    For Each cel In Target
        If Left(cel.Value, 1) = Chr(149) And Not cel.HasFormula Then
            cel.Characters(1, 1).Font.Color = vbRed
        End If
    Next cel
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Remarks:

1) If you already have code in the Worksheet_Change event procedure, you must add the above code (except for the Private Sub and End Sub lines) to the existing event procedure, preferably at the beginning. It is not allowed to have multiple Worksheet_Change event procedures for the same worksheet.
2) If the worksheet is protected, you must unprotect it at the beginning of the code, and reprotect it at the end.

---

For the entire workbook:

Activate the Visual Basic Editor.
Double-click ThisWorkbook.
Copy the following code into the ThisWorkbook module:

Code: Select all

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim cel As Range
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    For Each cel In Target
        If Left(cel.Value, 1) = Chr(149) And Not cel.HasFormula Then
            cel.Characters(1, 1).Font.Color = vbRed
        End If
    Next cel
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

The same remarks as above apply.

[ABabeNChrist]

Your awesome, thanks Hans