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

FindingMyself

New Member
Joined
Jun 27, 2011
Messages
19
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?
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
..Then Cell.Interior.Color = vbYellow

Why use a macro for something Conditional Formatting can handle, though?
 
Upvote 0
..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
 
Upvote 0
And what's the problem?


Ah, my mistake. I placed the code in the wrong sheet. Thanks to both of you! :biggrin: 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.
 
Upvote 0
If you mean to color every time new value appears in G column, then:
Code:
[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]
 
Last edited:
Upvote 0
If you mean to color every time new value appears in G column, then:
Code:
[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.
 
Upvote 0
Fill where? First, code removes any fill, and then it looks again for values in G column.
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top