Please help me on Macro FormatConditions

[albertxu]

I tried to write a macro to format my excel file.

There are numbers in column G and the numbers range between 1 to 99 (all are integers). My target is to shade the row with color green when the number is equal to or less than 30, grassgreen when it's between 31 and 40, yellow when it's larger than 41 but less than 45. my code is as follows but it did not work, can anyone give me help?

Thanks a lot.

    ActiveSheet.UsedRange.Select
    
    Selection.FormatConditions.Delete
    
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$G2<45.1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$G2<40.1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$G2<30.1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

 

[sericom]

Might be to do with the range it's using

Try changing
ActiveSheet.UsedRange.Select

to something like

ActiveSheet.Range("A2:M50").Select

 

[albertxu]

But I have hundreds of excel file need to be done with this macro, those files have different rows.

Might be to do with the range it's using

Try changing
ActiveSheet.UsedRange.Select

to something like

ActiveSheet.Range("A2:M50").Select

 

[sericom]

That was a test to see if it fixed your problem, as you didn't say the exact issue.

 

[ask2tsp]

this seems to work

Sub condFormat()
    ActiveSheet.UsedRange.Select
    
    Selection.FormatConditions.Delete

    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:=41, Formula2:=45
    Selection.FormatConditions(1).Interior.Color = RGB(255, 255, 0) 'yellow
    
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="31", Formula2:="40"
    Selection.FormatConditions(2).Interior.Color = RGB(0, 153, 51) 'grass green
    
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="31"
    Selection.FormatConditions(3).Interior.Color = RGB(0, 255, 0) 'green
End Sub