Conditional Formatting via Macro

[siddo]

Hi, I've trying to build a macro that, once we click, will automatically put the conditional formatting in the page. I have added the excel sheet to the google drive for your reference. It would be great if anyone can help me or guide me create a macro for this - New Microsoft Excel Worksheet.xlsx

 

[rlv01]

I cannot open insecure internet files, but here is an example.

'
' This example will set a format condition to change the cell color
' for any cell greater than 20 and less than 50 for range A2 to A100
'

Sub SetFormatConditionsExample()
    Dim FCRange As Range
    Dim FormulaStr As String
    Dim WS As Worksheet
    
    Set WS = ActiveSheet                    'define worksheet containing format conditions.
    
    'a) Define the cell range
    Set FCRange = WS.Range("A2:A100")
    
    With FCRange.FormatConditions
        
        'b) Delete the existing format conditions
        .Delete
        
        'c) Define the formula
        FormulaStr = "=AND($A2>20,$A2<50)"         'Any formula that resolves to True or False"
        
        
        'd) Add the new FormatCondition and define the formatting
        With .Add(Type:=xlExpression, Formula1:=FormulaStr)
            .StopIfTrue = True
            .Interior.Color = vbGreen
            .Font.Color = vbBlack
        End With
    End With
End Sub

 

[siddo]

I cannot open insecure internet files, but here is an example.

'
' This example will set a format condition to change the cell color
' for any cell greater than 20 and less than 50 for range A2 to A100
'

Sub SetFormatConditionsExample()
    Dim FCRange As Range
    Dim FormulaStr As String
    Dim WS As Worksheet
   
    Set WS = ActiveSheet                    'define worksheet containing format conditions.
   
    'a) Define the cell range
    Set FCRange = WS.Range("A2:A100")
   
    With FCRange.FormatConditions
       
        'b) Delete the existing format conditions
        .Delete
       
        'c) Define the formula
        FormulaStr = "=AND($A2>20,$A2<50)"         'Any formula that resolves to True or False"
       
       
        'd) Add the new FormatCondition and define the formatting
        With .Add(Type:=xlExpression, Formula1:=FormulaStr)
            .StopIfTrue = True
            .Interior.Color = vbGreen
            .Font.Color = vbBlack
        End With
    End With
End Sub

Hi Thank you for your reply, I have 5 conditions to this, The file I have uploaded is on google drive but I'm uploading an image as well for your reference

 

[rlv01]

Like I said, I cannot open random files off the internet.

Hi Thank you for your reply, I have 5 conditions to this, The file I have uploaded is on google drive but I'm uploading an image as well for your reference

You can extend my example to add more rules:

'
' This example will set a format condition to change the cell color
' for any cell greater than 20 and less than 50 for range A2 to A100 and range B2 to B100
'

Sub SetFormatConditionsExample()
    Dim FCRange As Range
    Dim FormulaStr As String
    Dim WS As Worksheet
    
    Set WS = ActiveSheet                    'define worksheet containing format conditions.
    

'Column A rules
    'a) Define the cell range
    Set FCRange = WS.Range("A2:A100")
    
    With FCRange.FormatConditions
        
        'b) Delete the existing format conditions
        .Delete
        
        'c) Define the formula
        FormulaStr = "=AND($A2>20,$A2<50)"         'Any formula that resolves to True or False"
        
        
        'd) Add the new FormatCondition and define the formatting
        With .Add(Type:=xlExpression, Formula1:=FormulaStr)
            .StopIfTrue = True
            .Interior.Color = vbGreen
            .Font.Color = vbBlack
        End With
    End With

'Column B rules
    'a) Define the cell range
    Set FCRange = WS.Range("B2:B100")
    
    With FCRange.FormatConditions
        
        'b) Delete the existing format conditions
        .Delete
        
        'c) Define the formula
        FormulaStr = "=AND($B2>20,$B2<50)"         'Any formula that resolves to True or False"
        
        
        'd) Add the new FormatCondition and define the formatting
        With .Add(Type:=xlExpression, Formula1:=FormulaStr)
            .StopIfTrue = True
            .Interior.Color = vbYellow
            .Font.Color = vbBlack
        End With
    End With

End Sub