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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
..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,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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