# if the last cell of each row is equal in all 4 tables row then replace all the data of all that row to 1(In 4 tables)

There are 4 tables
if the last cell of each row is equal in all 4 tables row then replace all the data of  all that row to 1
see the result in sheet2
we have to do this in sheet1 not in sheet2, we have to do this in sheet1 only,I have pasted the result in sheet2 only for understanding purpose
this highlighted colour is only for understanding purpose in actual file there is no highlighted colour
we have to do all this by Vba only
Sample-File.xlsm
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Here is the code:

``````Const MAX_TABLE As Integer = 4

Public Sub Replace()
Dim ws As Worksheet
Dim Mark As Range

Dim r As Integer
Dim c As Integer
Dim m As Integer

Dim TotalRow As Integer
Dim TotalCol As Integer
Dim LastCol As Integer
Dim CurrentRow As Integer

Dim CurrentNumber As Integer
Dim IsMatched As Boolean

Set ws = ThisWorkbook.Worksheets("Sheet1")

TotalRow = ws.Cells(1, 1).CurrentRegion.Rows.Count + 1 'Accounted for empty row
TotalCol = ws.Cells(1, 1).CurrentRegion.Columns.Count

For r = 2 To TotalRow - 1
LastCol = TotalCol
For c = 2 To TotalCol
Set Mark = ws.Cells(r, c)
If IsEmpty(Mark) Then
LastCol = c - 1
Exit For
End If
Next c

CurrentNumber = ws.Cells(r, LastCol)
IsMatched = True
For m = 1 To MAX_TABLE - 1
CurrentRow = m * TotalRow + r
If CurrentNumber <> ws.Cells(CurrentRow, LastCol) Then
IsMatched = False
Exit For
End If
Next m

If IsMatched Then
For c = 1 To LastCol
For m = 0 To MAX_TABLE - 1
CurrentRow = m * TotalRow + r
ws.Cells(CurrentRow, c) = 1
Next m
Next c
End If
Next r
End Sub
``````

Experts Exchange Solution brought to you by