Excel - colour a cell if one cell in the row has red text-CodePudding

I usually merge excel files inputs and to check if there has been a change, the people highlight in red the modifiied cells. This means that I have to scroll visually the whole Excel file to search for these red cells with the risk of missing them. Is there a way in visual basic to apply a formatting to the rows if a cell in the row has a red text (or a text that is not the default black)? Thank you in advance Carmen

CodePudding user response：

1. Find cells which have all the text colored in red

Similar question solved here:

CodePudding user response：

Try this code:

Sub SubRedRows()
    
    'Declarations.
    Dim RngData As Range
    Dim RngTarget As Range
    Dim DblStart As Double
    Dim StrAddress As String
    
    'Settings.
    Set RngData = Range("A1:J10")
    Set RngTarget = RngData(1, 1)
    
    'Setting the format to be searched.
    Application.FindFormat.Clear
    Application.FindFormat.Font.Color = vbRed
    
    'Covering the whole RngData.
    Do
        
        'Setting RngTarget as the first cell after RngTarget with the desired format.
        Set RngTarget = Cells.Find(What:="", _
                                   After:=RngTarget, _
                                   LookIn:=xlFormulas, _
                                   LookAt:=xlPart, _
                                   SearchOrder:=xlByRows, _
                                   SearchDirection:=xlNext, _
                                   MatchCase:=False, _
                                   SearchFormat:=True _
                                  )
        
        'Checking if DblStart has been already set.
        If DblStart = 0 Then
            'If RngTarget is nothing, no range with the desired format has been found so the macro is terminated
            If RngTarget Is Nothing Then Exit Sub
            'Setting DblStart.
            DblStart = RngTarget.row
        Else
            'If RngTarget's row is equal to DblStart, the loop is left.
            If RngTarget.row = DblStart Then GoTo CP_Exit_Loop
        End If
        
        'Adding the address of the row of the cell just found to StrAddress.
        StrAddress = StrAddress & "," & Intersect(RngTarget.EntireRow, RngData).Address
        
        'Setting RngTarget for the next row.
        Set RngTarget = Cells(RngTarget.row   1, 1)
    Loop
    
CP_Exit_Loop:
    
    'Setting the format of the range found.
    Range(Right(StrAddress, Len(StrAddress) - 1)).Font.Color = vbRed
    
    'Clearing up the find format.
    Application.FindFormat.Clear
    
End Sub

You'll need to change the setting of RngData accordingly to your need. It will work even on ranges with multiple areas (still it will turn the font red on all the intersections with a given row and any area even if only one of those intersection has 1 or more cells with red font).