excel - conditional formatting - only for the last 50 rows of the tabl

[shirazsafa]

hi
Please help me if possible
My formula is not working properly -
Because the range for formatting is variable
When the start of the range is from row 100 onwards.
Because in the formula written in conditional formatting, the address is for the previous rows
The format no longer works

Sub formatting()
Dim rng As Range
Dim lr As Long

 lr = Sheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).Row - 40

  Range("W" & lr).Resize(50, 1).Select
'Suppose this is limited from row 200 to 250

  Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND($G2<>"""";$W2<>"""";$W2<4)"
'Because cell $G2 is mentioned in the formula. And now this cell is not in the area. For this reason, the above formula fails

    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
End Sub

thx . safa

 

[Vogateer]

If you make the row a variable, it seems like you could handle it like this:

Sub formatting()
Dim rng As Range
Dim lr As Long, format_rng As Range, start_row As Long

 lr = Sheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).Row - 49 ' why was it 40 instead of 49?

  Set format_rng = Range("W" & lr).Resize(50, 1)
  start_row = format_rng(1, 1).Row
'Suppose this is limited from row 200 to 250

  'Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND($G2<>"""";$W2<>"""";$W2<4)"
    format_rng.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND($G" & start_row & "<>"""",$W" & start_row & "<>"""",$W" & start_row & "<4)"
'Because cell $G2 is mentioned in the formula. And now this cell is not in the area. For this reason, the above formula fails

    format_rng.FormatConditions(format_rng.FormatConditions.Count).SetFirstPriority
    With format_rng.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With format_rng.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
End Sub

 

[shirazsafa]

If you make the row a variable, it seems like you could handle it like this:

Sub formatting()
Dim rng As Range
Dim lr As Long, format_rng As Range, start_row As Long

 lr = Sheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).Row - 49 ' why was it 40 instead of 49?

  Set format_rng = Range("W" & lr).Resize(50, 1)
  start_row = format_rng(1, 1).Row
'Suppose this is limited from row 200 to 250

  'Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND($G2<>"""";$W2<>"""";$W2<4)"
    format_rng.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND($G" & start_row & "<>"""",$W" & start_row & "<>"""",$W" & start_row & "<4)"
'Because cell $G2 is mentioned in the formula. And now this cell is not in the area. For this reason, the above formula fails

    format_rng.FormatConditions(format_rng.FormatConditions.Count).SetFirstPriority
    With format_rng.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With format_rng.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
End Sub

I really don't know how to thank you. Thank you, a thousand times,

 

[Vogateer]

I really don't know how to thank you. Thank you, a thousand times,

That's a great thank you. And you're welcome!