Option Explicit
Sub get_values()
Dim Sh1 As Worksheet
Dim rg_B As Range, rg_H As Range
Dim Rg_All As Range, Nro%
Dim Ro%, i%, st
Set Sh1 = Sheets("Sheet1")
Ro = Sh1.Cells(Rows.Count, 2).End(3).Row
Set Rg_All = Sh1.Range("A7:H" & Ro)
Rg_All.Borders.LineStyle = xlNone
Rg_All.Interior.ColorIndex = xlNo
Rg_All.Columns("G:H").ClearContents
For i = 7 To Ro
Select Case Cells(i, 2)
Case "متزوج", "ارمله1": st = 2
Case "متزوج1", "ارمله2": st = 4
Case "متزوج2": st = 6
Case Else: st = "اعزب"
End Select
Cells(i, 7) = st
Next
Cells(7, 8).Resize(Ro - 6).Formula = "=IF(C8=""نقابى"",100,"""")"
With Sh1.Range("A7:H" & Ro)
.HorizontalAlignment = 1
.InsertIndent 1
.Borders.LineStyle = 1
.Interior.ColorIndex = 20
.Font.Size = 14
.Value = .Value
End With
End Sub