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]

I'm not sure I understand where the cell color fits in, but if you simply want to ensure the sequence jj, ff, hh is maintained throughout column A starting in cell A1 (adjust to fit), try this:

Sub RepairPattern()
Dim rng As Range, lRw As Long

lRw = Range("A" & Rows.Count).End(xlUp).Row
Set rng = Range("A1", "A" & lRw)
For i = rng.Rows.Count To 1 Step -1
    If rng.Rows(i).Value = "ff" And rng.Rows(i + 1).Value <> "hh" Then
        rng.Rows(i + 1).Insert shift:=xlDown
        rng.Rows(i + 1).Value = "hh"
    End If
Next i
End Sub

 

[bhushanpc]

Hey Joe,
Thanx for the macro. u got ir right i want to maintain the sequence jj,ff,hh. the problem being the initial word of the text string Continent in each cell remains jj,ff, hh but the following text changes eg.
jj - xyz
ff- xyz
hh- xyz

hence i have highlighted the cell with diff color using conditional formatting. now i want to insert the cell with hh ext string where it is missing in the sequence, same for ff. the jj cell appears all the way down it is not missing anywhere in the sequence.

 

[JoeMo]

Can you post an example of what your spreadsheet looks like and the result you want? Use Excel Jeanie:
http://www.excel-jeanie-html.de/index.php?f=1

 

[bhushanpc]

Hey Joe,

Hope this eg will help u understand better
1st row Address
2nd row Phone
3rd row Mobile/Cell Phone
this the sequence i want but in some cases the phone is not mentioned only Mobile/Cell phone is mentioned here i want to insert the Phone row and where the mobile is not mentioned insert the same.
the sequence should remain essentially the same. as the address , phone n mobile will be different for every entry i dont know how to do it. even if it is possible to insert rows with only the text as phone or mobile it would be help full. since finding which entry is missing in more than 1000 rows is really a pain in the neck. i am sorry i am not able to post the actual data as it belongs to my company.
Pls provide some solution as i have to do this day in day out.

 

[bhushanpc]

Joe hope this will be help full
A
1 Address: L-33, APMC Market, Market-1, Phase-2, Vashi, Navi Mumbai - 400 705, India
2 Phone: +(91)-(22)-27668278/27666669 Fax: +(91)-(22)-67905633
3 Mobile / Cell Phone: +(91)-9324676766
4 Address: 52, 2nd Floor, Shop No. 11, Tamil Sangam Road, Madurai - 625 003, India
5 Mobile / Cell Phone: +(91)-9865175357/9976657568
6 Address: No. 170, Shukan Mall, Science City Road, Sola, Ahmedabad - 380 060, India
7 Mobile / Cell Phone: +(91)-9714714744/9558297707
8 Address: C4H/14, Janakpuri, New Delhi - 110 058, India
9 Phone: +(91)-(11)-25618613/25548147 Fax: +(91)-(11)-25536472
10 Mobile / Cell Phone: +(91)-9810046890
11 Address: UGF- 18, Parsvnath Plaza, No. 1 Delhi Road, Moradabad - 244 001, India
12 Phone: +(91)-(591)-2482555 Fax: +(91)-(591)-2482556
13 Mobile / Cell Phone: +(91)-9720130555
14 Address: Old Jessore Road, Near A. S. G. Biochem Private Limited Ganganagar, Kolkata - 700 132, India
15 Mobile / Cell Phone: +(91)-9433060675

 

[bhushanpc]

Hi joe
as you see im my earlier post the rows 4,5,6n7 the entry Phone is missing
i would the data in the format

A
1 Address: L-33, APMC Market, Market-1, Phase-2, Vashi, Navi Mumbai - 400 705, India
2 Phone: +(91)-(22)-27668278/27666669 Fax: +(91)-(22)-67905633
3 Mobile / Cell Phone: +(91)-9324676766
4 Address: 52, 2nd Floor, Shop No. 11, Tamil Sangam Road, Madurai - 625 003, India
5 Phone
6 Mobile / Cell Phone: +(91)-9865175357/9976657568
7 Address: No. 170, Shukan Mall, Science City Road, Sola, Ahmedabad - 380 060, India
8 phone
9 Mobile / Cell Phone: +(91)-9714714744/9558297707
10 Address: C4H/14, Janakpuri, New Delhi - 110 058, India
11 Phone: +(91)-(11)-25618613/25548147 Fax: +(91)-(11)-25536472
12 Mobile / Cell Phone: +(91)-9810046890
13 Address: UGF- 18, Parsvnath Plaza, No. 1 Delhi Road, Moradabad - 244 001, India
14 Phone: +(91)-(591)-2482555 Fax: +(91)-(591)-2482556
15 Mobile / Cell Phone: +(91)-9720130555
16 Address: Old Jessore Road, Near A. S. G. Biochem Private Limited Ganganagar, Kolkata - 700 132, India
17 phone
18 Mobile / Cell Phone: +(91)-9433060675

 

[JoeMo]

Do the cell entries start with either "Address", "Phone" or "Mobile" or are you just adding those words to your post for clarity?

 

[bhushanpc]

yes they start as mentioned

 

[JoeMo]

yes they start as mentioned

The code below looks for the key words "Address", "Phone", "Mobile" in each cell in column A, then if one of these is found in the cell it looks to see if the cell below it has the next keyword in this sequence. If not, it inserts a new cell and enters that keyword in it as a placeholder. There is a constant (Const) parameter at the top of the code called nLoops. This can be adjusted upward if needed to cycle through this process until all cells have the correct sequence. Adjusting it upward increases the execution time as a trade-off, and may add some additional cells filled with keywords at the end of the range. The code assumes your data begin in cell A1. Adjust this in the code if that's not the case.

I have only tested this in a cursory fashion so it may need a few tweaks.

Sub RepairPattern()
Dim rng As Range, lRw As Long, vArr As Variant, ctr As Long
Const sA = "Address"
Const sP = "Phone"
Const sM = "Mobile"
Const nLoops = 4  'Increase ctr for more loops as needed - will also increase execution time

Do
    ctr = ctr + 1
    lRw = Range("A" & Rows.Count).End(xlUp).Row
    Set rng = Range("A1", "A" & lRw)
    vArr = Array(sA, sP, sM)
    
    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, 1
                        If InStr(1, rng.Cells(i).Offset(1, 0).Value, vArr(j + 1)) > 0 Then
                            Exit For
                        Else
                            rng.Cells(i).Offset(1, 0).Insert shift:=xlDown
                            rng.Cells(i).Offset(1, 0).Value = vArr(j + 1)
                        End If
                    Case Else
                        If InStr(1, rng.Cells(i).Offset(1, 0).Value, sA) > 0 Then
                            Exit For
                        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
Loop Until ctr >= nLoops
On Error Resume Next
rng.SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
End Sub