Find any cell in Row=

[AlexanderBB]

Is there an nice Excel way to see if any cell in a row contains the string "See Comment" ?
Or should I just loop through all of them ?

 

[arun_eagertolearn]

Assuming content is in row 1 (from A1 to J1)

as the last column, pls put formula K1=concat(A1:J1)

then, in the next column L1=IFERROR(IF(FIND("see comment",K1)>0,"Yes","No"),"No")

all rows with yes in L1 will have "see comment" in them


alternatively, you may also use conditional formatting to highlight those cells which have "see comment" in them

 

[Alex Blakenburg]

Using the same range as Arun try:

=IF(COUNTIFS($A1:$J1,"*see comment*")>0,"Yes","""No")

 

[AlexanderBB]

The columns involved are 9 to 18, now not the entire row, I ended up using

For i = 9 To 18 ' I to R
            If .Cells(ExcelRow, i) = "See Comment" Then
                 .Cells(ExcelRow, i).Font.Color = 16711935
            End If
Next

Which seems to work ok. I wasn't able to get the suggestions to compile.
Maybe goes in a worksheet cell? Sorry, should have said this was in a module.
I'd use it if I could! (I've never used a worksheet formula)

 

[Alex Blakenburg]

Your method highlights each cell in the row that = "See Comment", which is different to flagging if it appears anywhere in a row.
The other methods are both aimed at being used in the worksheet although you could put them in a worksheetfunction.
They also use "contains" rather than "=" so will still count it if there is more text surrounding the "See Comment"
The countifs version is not case sensitive both your method and using the find function are case sensitive.

 

[AlexanderBB]

I'll run some casing tests and use case insensitive if needed. And change to Instr. You're right - maybe a random space could have got in.