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

[malleshg24]

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.

[Leith Ross]

Hello malleshg24,

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

[malleshg24]

No sir, there will not. Thanks

mg

[malleshg24]

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

[p45cal]

Code in the attached, run by button click on Sheet1:
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

[malleshg24]

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

[p45cal]

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