Change font color for a row of text in cell which contains a certain value-CodePudding

I am writing a check in/out program in excel and have gotten te request that if a line contains "|0|" it should get a different font color.

I've tried with Instr and Cells().Characters but I cannot seem to figure out how to do it.

The cells can have a variety of rows of text. Which is easy enough to solve with splitting them on a return and having a for loop loop, but I cannot seem to figure out how to assign a different font color to a row of text that contains the required value.

Image for illustration of the data:

How do I best solve this?

CodePudding user response：

my guess:

Private Sub ExampleSub()
    Dim cell As Range
    For Each cell In Selection
        If HasMySymbols(cell.Value) Then
            cell.Font.Color = vbGreen
        Else
            cell.Font.ColorIndex = xlAutomatic
            cell.Font.TintAndShade = 0
        End If
    Next cell
End Sub

Private Function HasMySymbols(ByVal somestring As String) As Boolean
    HasMySymbols = InStr(1, somestring, "|0|") > 0
End Function

Сonditional formatting has an advantage when it's nice to keep the cell's previous state, otherwise the macro will overwrite the font color forever

CodePudding user response：

Try this. It works for me.

Sub ChangeRowFontColour()
    
    Dim rng As Range
    Dim TextToFind As String
    Dim FirstFound As String
    
    TextToFind = "hello"

        With ActiveSheet.UsedRange
            Set rng = .Cells.Find(TextToFind, LookIn:=xlValues)
            
            If Not rng Is Nothing Then
            FirstFound = rng.Address
                Do
                    rng.EntireRow.Font.ColorIndex = 3
                    Set rng = .FindNext(rng)
                Loop While Not rng Is Nothing And rng.Address <> FirstFound
            End If
        End With
                        
End Sub