Change fill color if cell value is greater than other cell value

[FindingMyself]

So I'm trying to write a macro that will change the fill color of the cell in a column exceeds the value of another cell, here is what I have so far.

For Each Cell In Range("G2", Range("G65536").End(xlUp))
If Cell.Value > Sheets("Settings").Range("C2").Value Then

End If

How can I make it change the fill color of the cell in that column that exceeds the value of another cell?

 

[mvptomlinson]

..Then Cell.Interior.Color = vbYellow

Why use a macro for something Conditional Formatting can handle, though?

 

[FindingMyself]

..Then Cell.Interior.Color = vbYellow

Why use a macro for something Conditional Formatting can handle, though?

Because I can't seem to reference cells in another worksheet for conditional formatting.

Also my code seems to have some problems trying to run:

For Each Cell In Range("G2", Range("G65536").End(xlUp))
If Cell.Value > Sheets("Settings").Range("C2").Value Then
Cell.Interior.Color = vbRed
End If
Next Cell

 

[Sektor]

And what's the problem?

 

[FindingMyself]

And what's the problem?

Ah, my mistake. I placed the code in the wrong sheet. Thanks to both of you! However, is there a way to make it change the fill color the instant a value is filled in? I'm using Private Sub Worksheet_Activate() now and it only does the fill if you switch to another sheet and back to it before it shows up.

 

[Sektor]

If you mean to color every time new value appears in G column, then:

[COLOR="Blue"]Private[/COLOR] [COLOR="Blue"]Sub[/COLOR] Worksheet_Change([COLOR="Blue"]ByVal[/COLOR] Target [COLOR="Blue"]As[/COLOR] Range)
    [COLOR="Blue"]Dim[/COLOR] cell [COLOR="Blue"]As[/COLOR] Range
    [COLOR="Blue"]If[/COLOR] [COLOR="Blue"]Not[/COLOR] Intersect(Target, Columns("G:G")) [COLOR="Blue"]Is[/COLOR] [COLOR="Blue"]Nothing[/COLOR] [COLOR="Blue"]Then[/COLOR]
        Range("G2", Range("G65536").End(xlUp)).Interior.Color = xlNone
        [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] cell [COLOR="Blue"]In[/COLOR] Range("G2", Range("G65536").End(xlUp))
            [COLOR="Blue"]If[/COLOR] cell.Value > 10 [COLOR="Blue"]Then[/COLOR]
                cell.Interior.Color = vbRed
            [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
        [COLOR="Blue"]Next[/COLOR]
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[FindingMyself]

If you mean to color every time new value appears in G column, then:

[COLOR="Blue"]Private[/COLOR] [COLOR="Blue"]Sub[/COLOR] Worksheet_Change([COLOR="Blue"]ByVal[/COLOR] Target [COLOR="Blue"]As[/COLOR] Range)
    [COLOR="Blue"]Dim[/COLOR] cell [COLOR="Blue"]As[/COLOR] Range
    [COLOR="Blue"]If[/COLOR] [COLOR="Blue"]Not[/COLOR] Intersect(Target, Columns("G:G")) [COLOR="Blue"]Is[/COLOR] [COLOR="Blue"]Nothing[/COLOR] [COLOR="Blue"]Then[/COLOR]
        Range("G2", Range("G65536").End(xlUp)).Interior.Color = xlNone
        [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] cell [COLOR="Blue"]In[/COLOR] Range("G2", Range("G65536").End(xlUp))
            [COLOR="Blue"]If[/COLOR] cell.Value > 10 [COLOR="Blue"]Then[/COLOR]
                cell.Interior.Color = vbRed
            [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
        [COLOR="Blue"]Next[/COLOR]
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

It works great, thanks. But it seems that even after a cell value is deleted the fill still remains.

 

[Sektor]

Fill where? First, code removes any fill, and then it looks again for values in G column.

 

[FindingMyself]

Never mind, it works now. Thanks!

 

[Sektor]

Not at all!