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?

  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,842

    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,842

    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,842

    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,842

    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