Put a value in a cell if any cell in a row of cells has a colored font

[Stovey31]

HI, I want to be able to return a value to a cell if any of the cells in the same row have red font. For example as columns B and D have red font I would like to have D2 and D4 return the text "Update" and any other cells in D where there is no red font either stay blank or return text "No changes"

	A	B	C	D
1	ETA	Container	Comments
2	25/4	abcd123456	As booked
3	16/2	ghfd58562
4	TBC		Offloaded
5	1/3	defg5894

Thanks for any help you can provide.

 

[My Aswer Is This]

I see no red font in column D
You said:
as columns B and D have red font

I see red font in column B and C

 

[Stovey31]

Sorry.....my bad. should read B & C

 

[Leo Skywalker]

Hi there;

Create the UDF:

Public Function CountCellsWithRedFontColor(Range1 As Range) As Integer
Application.Volatile
Dim myRng As Range
For Each myRng In Range1
    If myRng.Font.Color = vbRed Then
        CountCellsWithRedFontColor = CountCellsWithRedFontColor + 1: Exit Function
    End If
Next
End Function

Then in D2:

=IF(CountCellsWithRedFontColor(A2:C2),"Update","No changes")

and copy down.

 

[My Aswer Is This]

Try this:

Sub Check_For_Red_Font()
'Modified  10/17/2022  11:06:55 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 2 To Lastrow
        If Cells(i, 2).Font.Color = vbRed Or Cells(i, 3).Font.Color = vbRed Then
            Cells(i, 4).Value = "Update"
        Else
            Cells(i, 4).Value = "No Changes"
        End If
    Next
Application.ScreenUpdating = True
End Sub