how to insert rows based on cell color

[bhushanpc]

I am working on sheet which has approx 1000 rows. the data is quite structured.
A
1 jj
2 ff
3 hh
4 jj
5 ff
6 jj
7 hh
this how how data appears. i have highlighted the cell by using conditional formating. now i want to insert rows with text hh where it is missing based on the color of the cell so that the data after inserting the cell with cc appears as follows.
A
1 jj
2 ff
3 hh
4 jj
5 ff
6 hh
7 jj
8 ff
9 hh

Kindly ley me know if there is way i can do it in excel by a formula or any macro is available to do the same. i have near to zero knowledge of VBA .

Awaiting your feedback, Thanx in advance.

 

[JoeMo]

Here's another version that doesn't require looping. Again not well tested by me. Both versions insert cells as needed rather than entire rows. If you have data in columns B or beyond that line up with your column A entries then change ".insert" to ".entirerow.insert" throughout both versions.

Sub RepairPattern2()
Dim rng As Range, lRw As Long, vArr As Variant
Const sA = "Address"
Const sP = "Phone"
Const sM = "Mobile"

lRw = Range("A" & Rows.Count).End(xlUp).Row
Set rng = Range("A1", "A" & lRw)
vArr = Array(sA, sP, sM)
'Remove any empty cells in rng
On Error Resume Next
rng.SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
On Error GoTo 0

For i = rng.Rows.Count To 1 Step -1
    For j = 0 To 2
        If InStr(1, rng.Cells(i).Value, vArr(j)) > 0 Then
            Select Case j
                Case 0
                    If InStr(1, rng.Cells(i).Offset(1, 0).Value, vArr(j + 1)) > 0 Then
                        Exit For
                    ElseIf InStr(1, rng.Cells(i).Offset(1, 0).Value, vArr(j)) > 0 Then
                        rng.Cells(i).Offset(1, 0).Resize(2, 1).Insert shift:=xlDown
                        rng.Cells(i).Offset(1, 0).Value = vArr(j + 1)
                        rng.Cells(i).Offset(2, 0).Value = vArr(j + 2)
                    Else
                        rng.Cells(i).Offset(1, 0).Insert shift:=xlDown
                        rng.Cells(i).Offset(1, 0).Value = vArr(j + 1)

                    End If
                Case 1
                    If InStr(1, rng.Cells(i).Offset(1, 0).Value, vArr(j + 1)) > 0 Then
                        Exit For
                    ElseIf InStr(1, rng.Cells(i).Offset(1, 0).Value, vArr(j)) > 0 Then
                        rng.Cells(i).Offset(1, 0).Resize(2, 1).Insert shift:=xlDown
                        rng.Cells(i).Offset(1, 0).Value = vArr(j + 1)
                        rng.Cells(i).Offset(2, 0).Value = sA
                    Else
                        rng.Cells(i).Offset(1, 0).Insert shift:=xlDown
                        rng.Cells(i).Offset(1, 0).Value = vArr(j + 1)

                    End If
                Case 2
                    If InStr(1, rng.Cells(i).Offset(1, 0).Value, sA) > 0 Then
                        Exit For
                    ElseIf InStr(1, rng.Cells(i).Offset(1, 0).Value, vArr(j)) > 0 Then
                        rng.Cells(i).Offset(1, 0).Resize(2, 1).Insert shift:=xlDown
                        rng.Cells(i).Offset(1, 0).Value = sA
                        rng.Cells(i).Offset(2, 0).Value = sP
                    Else
                        rng.Cells(i).Offset(1, 0).Insert shift:=xlDown
                        rng.Cells(i).Offset(1, 0).Value = sA
                    End If
                End Select
        End If
    Next j
Next i
End Sub

 

[bhushanpc]

Hi Joe,
Tried the code both with n w/o loop by changing the const by the one i require. the w/o loop works much better. but the problem in both case is with the sequence
Address
Phone
Mobile
when we run the macro it adds only the const mobile where it is missing but does not add const Phone. initially i thought this may be cause it did not have reference. so i added 3 rows at the start i.e A1,A2,A3 as address, phone n mobile resp for the reference sequence. but still the problem remains. also the macro adds additional cell with cont add, phone n mobile in between.

the code is working but has this small problem.

thank you for your continuing feedback n support

 

[bhushanpc]

another way which can be worked out is rather than working over the sequence, is there a way by which i could add a cell with the const phone after the cell with const Address where it is missing. e.g the data is in form

A
1 Address
2 Phone
3 Mobile
4 Address
5 Mobile
6 Address
7 Phone
8 Address
9 Mobile
10 Address
11 Phone
12 Mobile
13 Address
14 Mobile

if code is available by which i can insert the cell with const Phone after the address cell where it is missing ( as mentioned above). i want the data like

A
1 Address
2 Phone
3 Mobile
4 Address
5 Phone
6 Mobile
7 Address
8 Phone
9 Address
10 Phone
11 Mobile
12 Address
13 Phone
14 Mobile
15 Address
16 Phone
17 Mobile

 

[JoeMo]

Hi Joe,
Tried the code both with n w/o loop by changing the const by the one i require. the w/o loop works much better. but the problem in both case is with the sequence
Address
Phone
Mobile
when we run the macro it adds only the const mobile where it is missing but does not add const Phone. initially i thought this may be cause it did not have reference. so i added 3 rows at the start i.e A1,A2,A3 as address, phone n mobile resp for the reference sequence. but still the problem remains. also the macro adds additional cell with cont add, phone n mobile in between.

the code is working but has this small problem.

thank you for your continuing feedback n support

It works for me so I'm not able to assist further unless you can post a small sample of your data showing before and after you run the code (use the second one I posted).

 

[bhushanpc]

can you provide me ur email id or a way to attach the excel doc. i am not able to post it. i tried the link u have provide but its not working for me.

Plsssssssssssssss

 

[JoeMo]

can you provide me ur email id or a way to attach the excel doc. i am not able to post it. i tried the link u have provide but its not working for me.

Plsssssssssssssss

Send me a private message on this board and I will send you an email address.
Just click on my name in the upper left corner of this post and select private message.