VBA - Merging and Bordering of cells if condition met....

HTML Code:

---

Hi Team,
I need your help, Task is to read the cell values from Column C of input file on by one till LastRow.
        if range("c3") = "ABC" and range("c4") = "XYZ" then        insert a blank line below c4.and Range("C3:C4")  needs a Border as shown in Output sheet.
and in Column A3 mention Situation 1. merge cell ("A3:A4") Increase Situation 1,2,3 and so on...
Usually at last we will not come across abc and xyz simultaneously. then remaining data should bebordered like a box.
I am attaching input data file , I want the result in input sheet only, not in a output sheet.
Thanks for your help.
Regards,MG.

---

Hello malleshg24,

Could another "abc , xyz" group ever follow the "abc , abc , abc , abc" group?

No sir, there will not. Thanks

mg

Slight change one extra row while bordering, it will be ABC ,xyz and one blank row then all these cells row in Border,

Code in the attached, run by button click on Sheet1:

Code:

---

Sub Test()
Dim lr As Long, i As Long, sht As Worksheet, wbk As Workbook
Dim TopDataCellRow As Long, nextbr As Long, SituationCount As Long, cll As Range

Set wbk = ThisWorkbook
Set sht = wbk.Worksheets("Sheet1")
TopDataCellRow = 3
With sht
  lr = .Range("b65000").End(xlUp).Row
  nextbr = lr + 1
  For i = lr To TopDataCellRow Step -1
    If .Cells(i, 3).Value = "XYZ" Then
      If .Cells(i - 1, 3).Value = "ABC" Then
        .Cells(i + 1, 3).EntireRow.Insert
        lr = lr + 1
        With .Range(.Cells(nextbr, "C"), .Cells(i + 2, "B"))
          .BorderAround , 4
          .Offset(, -1).Resize(, 1).BorderAround , 4
          .Offset(, -1).Resize(, 1).MergeCells = True
          .Offset(, -1).Cells(1).Value = "¬`"
          nextbr = i + 2
        End With
      End If
    End If
    If i = TopDataCellRow Then
      With .Range(.Cells(nextbr - 1, 3), .Cells(TopDataCellRow, 2))
        .BorderAround , 4
        .Offset(, -1).Resize(, 1).BorderAround , 4
        .Offset(, -1).Resize(, 1).MergeCells = True
        .Offset(, -1).Cells(1).Value = "¬`"
      End With
      .Cells(TopDataCellRow - 1, "B").Resize(, 2).ClearContents
    End If
  Next i
  SituationCount = 0
  For Each cll In .Range("A" & TopDataCellRow & ":A" & lr).Cells
    If cll.Value = "¬`" Then
      SituationCount = SituationCount + 1
      cll.Value = "Situation " & SituationCount
    End If
  Next cll
End With
End Sub

---

Hi Leith /P45Cal

you guys are geneous,Thanks for help.
Is there a way to re-upload a workbook if there is any modification suggested by our BA. in quick reply don't find this Option.

Regards,
mg

Quote:

---

Originally Posted by malleshg24

Is there a way to re-upload a workbook if there is any modification suggested by our BA. in quick reply don't find this Option.

---

Only with Go Advanced which you can do from Quick Reply