Conditional formatting of a variable range

[Flavien]

A first code inserts new columns between the "Maxi Rating" and "MINI" columns.
As a result, the address of these columns is constantly changing.
In addition, this code will be used on different files, for which I would have a variable amount of lines (1 to 10 max).

I'd like to add conditional formatting for each active row based on the minimum and maximum range dimensions respectively ("G"; (i) and range ("H"; i).

This code is very slow and I don't know why, the code runs past the last non-empty one.

First try:

' Find the last row with data in column A

LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).row

' Loop through each row from 1 to LastRow

For i = 1 To LastRow
Set rng = Range(Range("Cote_Maxi").Offset(i, 1), Range("mini").Offset(i, -1)).row(

For Each c In rng

If c.Value < Range("Cote_mini").Offset(i, 0) Then c.Interior.Color = vbYellow
If c.Value > Range("Cote_maxi").Offset(i, 0) Then c.Interior.Color = vbRed

Next c

Next i

Second try:

' Find the last row with data in column A

LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).row



' Loop through each row from 1 to LastRow

For i = 1 To LastRow

Set rng = Range(Range("Cote_Maxi").Offset(i, 1), Range("mini").Offset(i, -1)).row(i)



rng.Select

Application.CutCopyMode = False

rng.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _

Formula1:="=Range(G;i)"

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

Selection.FormatConditions(1).StopIfTrue = False


Next i

I hope someone can help me.

Thank you in advance.