Page 1 of 2 12 LastLast
Results 1 to 10 of 11

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

This is a discussion on Change fill color if cell value is greater than other cell value within the Excel Questions forums, part of the Question Forums category; So I'm trying to write a macro that will change the fill color of the cell in a column exceeds ...

  1. #1
    New Member
    Join Date
    Jun 2011
    Posts
    19

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

    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?

  2. #2
    Board Regular
    Join Date
    Mar 2008
    Posts
    2,638

    Default Re: Change fill color if cell value is greater than other cell value

    ..Then Cell.Interior.Color = vbYellow

    Why use a macro for something Conditional Formatting can handle, though?
    Where to upload Excel files so threads don't take 20+ posts to get the correct answer from guessing:

    MediaFire: http://www.mediafire.com/
    FileFactory: http://www.filefactory.com/
    FileDropper: http://www.filedropper.com/

    Also, in your thread or in your signature TELL US WHAT VERSION OF EXCEL YOU'RE USING!!!

    I'm using Excel 2007. My solutions will be appropriate for that version unless you tell us otherwise.

  3. #3
    New Member
    Join Date
    Jun 2011
    Posts
    19

    Default Re: Change fill color if cell value is greater than other cell value

    Quote Originally Posted by mvptomlinson View Post
    ..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

  4. #4
    Board Regular
    Join Date
    May 2011
    Location
    Russia
    Posts
    1,651

    Default Re: Change fill color if cell value is greater than other cell value

    And what's the problem?

  5. #5
    New Member
    Join Date
    Jun 2011
    Posts
    19

    Default Re: Change fill color if cell value is greater than other cell value

    Quote Originally Posted by Sektor View Post
    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.

  6. #6
    Board Regular
    Join Date
    May 2011
    Location
    Russia
    Posts
    1,651

    Default Re: Change fill color if cell value is greater than other cell value

    If you mean to color every time new value appears in G column, then:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim cell As Range
        If Not Intersect(Target, Columns("G:G")) Is Nothing Then
            Range("G2", Range("G65536").End(xlUp)).Interior.Color = xlNone
            For Each cell In Range("G2", Range("G65536").End(xlUp))
                If cell.Value > 10 Then
                    cell.Interior.Color = vbRed
                End If
            Next
        End If
    End Sub
    Last edited by Sektor; Jul 13th, 2011 at 01:20 AM. Reason: Correction

  7. #7
    New Member
    Join Date
    Jun 2011
    Posts
    19

    Default Re: Change fill color if cell value is greater than other cell value

    Quote Originally Posted by Sektor View Post
    If you mean to color every time new value appears in G column, then:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim cell As Range
        If Not Intersect(Target, Columns("G:G")) Is Nothing Then
            Range("G2", Range("G65536").End(xlUp)).Interior.Color = xlNone
            For Each cell In Range("G2", Range("G65536").End(xlUp))
                If cell.Value > 10 Then
                    cell.Interior.Color = vbRed
                End If
            Next
        End If
    End Sub
    It works great, thanks. But it seems that even after a cell value is deleted the fill still remains.

  8. #8
    Board Regular
    Join Date
    May 2011
    Location
    Russia
    Posts
    1,651

    Default Re: Change fill color if cell value is greater than other cell value

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

  9. #9
    New Member
    Join Date
    Jun 2011
    Posts
    19

    Default Re: Change fill color if cell value is greater than other cell value

    Never mind, it works now. Thanks!

  10. #10
    Board Regular
    Join Date
    May 2011
    Location
    Russia
    Posts
    1,651

    Default Re: Change fill color if cell value is greater than other cell value

    Not at all!

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com