Find carriage return in cell & highlight row

[RigMan]

I know there's been discussions here before on how to search for carriage returns, but my needs are a little different, and being a newbie, I can't seem to figure it out myself.

I have a spreadsheet where one of the cells should contain only a single part number. But to save time, in certain instances the user has put one part number, hit Alt-Enter and entered a second part number. They do this because all the other information on the row is identical for both parts.

Now I need to search all cells for carriage returns, and if found, do one of two things;

Option A - Highlight the row in yellow. Then a user can go thru and manually copy the row, and change the existing row and the new row to contain a single part number only, or....

Option B - Automatically insert a new row after the offending row, and copy and paste the row. Then highlight both rows for the user to modify. This would at least save the user a little time.

Note there is also other columns that could contain a carriage return as well. I'd like to copy those rows as well. So all cells would need to be searched and either option A or B performed. But if multiple cells in the same row have Carriage returns, I only want to copy the row once.

Thanks in advance,
Ronnie

 

[Richard Schollar]

Hi Ronnie

You could use conditional formatting for this (in the example, I selected A1:B6 and went Format>Conditional Formatting):

Excel Workbook
	A	B
1	Name	Other
2	RichardSchollar	1 House
3	Jeremy	2 Houses
4	Daniel	1 Car
5	DonaldTrump	10 Houses20 Cars
6	Samuel	1 House1 Car
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =COUNTIF(1:1,"*"&CHAR(10)&"*")>0	Abc
B1	1. / Formula is =COUNTIF(1:1,"*"&CHAR(10)&"*")>0	Abc
A2	1. / Formula is =COUNTIF(2:2,"*"&CHAR(10)&"*")>0	Abc
B2	1. / Formula is =COUNTIF(2:2,"*"&CHAR(10)&"*")>0	Abc
A3	1. / Formula is =COUNTIF(3:3,"*"&CHAR(10)&"*")>0	Abc
B3	1. / Formula is =COUNTIF(3:3,"*"&CHAR(10)&"*")>0	Abc
A4	1. / Formula is =COUNTIF(4:4,"*"&CHAR(10)&"*")>0	Abc
B4	1. / Formula is =COUNTIF(4:4,"*"&CHAR(10)&"*")>0	Abc
A5	1. / Formula is =COUNTIF(5:5,"*"&CHAR(10)&"*")>0	Abc
B5	1. / Formula is =COUNTIF(5:5,"*"&CHAR(10)&"*")>0	Abc
A6	1. / Formula is =COUNTIF(6:6,"*"&CHAR(10)&"*")>0	Abc
B6	1. / Formula is =COUNTIF(6:6,"*"&CHAR(10)&"*")>0	Abc

EDIT: having selected A1:B6 and gone Format>Conditional Formatting, you only need to enter the following formula and then select your desired format (the other cells in A1:B6 will have the right formula applied to them)

=COUNTIF(1:1,"*"&CHAR(10)&"*")>0

 

[RigMan]

thanks Richard. That looks like it will work. I have never used conditional formatting before. If someone before me had applied conditional formating, is there a way to know where conditional formating has been applied?

For example, I used the conditional formating to apply cell shade yellow. Now that my row is yellow, if I didn't know conditional formating had been applied and try to go into Format-Cells and remove the yellow shading, it won't take. Because the conditional formating is overriding. How would I know that conditional formating is being used?

 

[RigMan]

Just for my education, my attempt at doing this with a macro is this;

Sub findrow()
Dim ws1 As Worksheet
Dim lastRw As Long, newRw As Long, i As Long
Dim lookFor As String
Dim found As Integer

lookFor = vbCrLf

Set ws1 = Sheets("Sheet1")

With ws1

lastRw = .Cells.Find(What:="*", _
After:=.Range("A1"), LookIn:=xlValues, _
SearchDirection:=xlPrevious).Row

For i = 1 To lastRw
    On Error Resume Next
    found = 0
    found = .Rows(i).Find(What:=lookFor, _
    After:=.Range("A" & i), LookIn:=xlValues, _
    SearchDirection:=xlNext)
    If found <> 0 Then
       ' highlight row here
    End If
Next

End With

End Sub

It doesn't seem to be finding the rows thought. And I don't know the syntax to highlight the row.

 

[RigMan]

Bump

Someone please look at my code above and tell me how to change to search for carriage returns, and how to change the shading on the row once found. Please???????

 

[Richard Schollar]

OK first things first:

How do you know CF has been applied? Maybe by using a helper column that contains exactly the same test as used in the CF formula itself - if True, then CF is applied, if False then CF isn't applied etc.

I'm pretty sure your code should work BUT! you aren't simply looking for a LineFeed character (ie Ascii character 10) - you are seraching on a vbCrLf which is a pairing of a Carriage Return (Ascii character 13) and a LineFeed. The code will probably work if you use vbLf instead - as this is only a LineFeed character.

Hope this helps!

Richard

 

[RigMan]

Richard
I tried vbLf, vbCrLf, and vbCr. None worked correctly. I even tried searching for plain text that I knew was in one of the cells, and it didn't find it. So I think my logic is flawed somewhere.

Your solution using the Conditional Formatting has worked fine for me. But it would be nice to figure out the vb macro as well. I thought the problem might be in the syntax of my Find function. Specifically, the After clause.

 

[Richard Schollar]

Declare found as a range and amend the code in the relevant parts to:

Dim found as Range

'...

Set found = .Rows(i).Find(What:=lookFor, _ 
     LookIn:=xlValues, Lookat:=xlPart, _
    SearchDirection:=xlNext)
If Not found Is Nothing 'ie lookfor's been found
 'apply formatting
End If

I didn't read carefully enough - Find returns an object if something is found, so you need your variable properly declared to handle it. The LookAt bit makes sure it will find matches within a cell. I deleted the After bit, as I don't think it was important.

Hope this works!

 

[RigMan]

Okay, that worked. Thanks Richard.

Here is the final code in case someone else needs it. It searchs all cells for a line feed and changes the background color of the row to yellow if found.

Sub findrow()
Dim ws1 As Worksheet
Dim lastRw As Long, newRw As Long, i As Long, p As Long
Dim lookFor As String
Dim found As Range

lookFor = vbLf

Set ws1 = Sheets("Sheet1")

With ws1

lastRw = .Cells.Find(What:="*", _
After:=.Range("A1"), LookIn:=xlValues, _
SearchDirection:=xlPrevious).Row

For i = 1 To lastRw
    On Error Resume Next
Set found = .Rows(i).Find(What:=lookFor, _
     LookIn:=xlValues, Lookat:=xlPart, _
    SearchDirection:=xlNext)
If Not found Is Nothing Then 'ie lookfor's been found
 'apply formatting
 .Rows(i).Interior.ColorIndex = 27
End If

Next

End With

End Sub