Conditional Formatting using a macro

[greatscottbattman]

I'm trying record a macro to do the following:
=G2/E2>10% Highlight Green
=G2/E2<(10%) Highlight red
The column I'd like highlighted is G2

My objective is to only draw attention to a variance of 10% and -10%

Does anyone have any tips of trying to write this in VBA cuz I'm stuck as the record button does not work.

As always, much appreciated.

 

[AlphaFrog]

    [G2].Interior.ColorIndex = xlNone
    If [E2] <> 0 Then
        If [G2] / [E2] < 0.1 Then
            [G2].Interior.ColorIndex = 3     'Red
        ElseIf [G2] / [E2] >= 0.1 Then
            [G2].Interior.ColorIndex = 4     'Green
        End If
    End If

 

[greatscottbattman]

Thank you for the quick response.
Looks like this will only apply to G2.
I'd like to have it go for a range of G2:G3000.

 

[AlphaFrog]

Can I ask why you are using VBA for this for this instead of Conditional Formatting?

 

[greatscottbattman]

I have about 80 sheets that can be updated any time by users. Then I have a macro that piles all 80 sheets on to one sheet. Each sheet has about 40 rows that a specific to people. But would like to have a summary report that would only show the +-10% highlighted. This can run at any time for the executive at a click of a button.
So the macro is building the one sheet with formatting and sorting and I'd like to add this conditional format if possible.

Hope that makes sense. I'd like the idea of a person clicking one button and all this happening on the summary sheet.

Everytime the macro runs, it deletes the summary sheet and creates a new one.

 

[AlphaFrog]

Would it work for you that the macro adds the Conditional Formatting formulas instead of it looping through the cells and setting the background colors individuality?

 

[greatscottbattman]

Yes. Please.
That would be ideal.

Thanks again.

 

[AlphaFrog]

    With Range("G2:G3000")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=RC7/RC5>=10%"
        .FormatConditions(1).Interior.ColorIndex = 4    'Green
        .FormatConditions.Add Type:=xlExpression, Formula1:="=RC7/RC5<10%"
        .FormatConditions(2).Interior.ColorIndex = 3    'Red
    End With

 

[greatscottbattman]

Brillant. Worked perfectly.
Thanks for you time and efforts.