Announcement

Collapse
No announcement yet.

Conditional formatting based on cell value in an adjacent column using VBA

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Conditional formatting based on cell value in an adjacent column using VBA

    I am trying to conditionally format a column of cells based upon multiple conditions and also the value in the cell to the left of it. I originally used conditional format in the sheet, but it bloated the size of the workbook to over 10 Mb. I have started a workseet change event and this is what I have at the moment. It successfully changes the color of the cell based on the values from Sheet1, but not on the cell to it's immediate left. I have highlighted what I tried in bold in the code below, but it is not working. This is the part that I am struggling with. i is equal to the row number e.g. G3:G434
    Code:
    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim onecent As Range
    Dim twocent As Range
    Dim threecent As Range
    Dim Cell
    Dim i As Integer
    
    On Error Resume Next
    Set onecent = Range("G3:G434")
        For Each Cell In onecent
            If Cell.Value = 0 Then
                Cell.Interior.ColorIndex = 2
            End If
            If Cell.Value > 0 And Cell.Value < Sheets("Sheet1").Range("B4") Then
                Cell.Interior.ColorIndex = 3
            End If
            If Cell.Value > Sheets("Sheet1").Range("C4") Then
                Cell.Interior.ColorIndex = 3
            End If
            
            If Cell.Value >= Sheets("Sheet1").Range("B4") And _
                    Cell.Value <= Sheets("Sheet1").Range("C4") Then
                Cell.Interior.ColorIndex = 4
            End If
        Next
    Set twocent = Range("H3:H434")
        For Each Cell In twocent
            If Cell.Value = "" Then
                Cell.Interior.ColorIndex = 2
            End If
            If Cell.Value > 0 And Cell.Value < Sheets("Sheet1").Range("B5") Then
                Cell.Interior.ColorIndex = 3
            End If
            If Cell.Value > Sheets("Sheet1").Range("C5") Then
                Cell.Interior.ColorIndex = 3
            End If
            
            If Cell.Value >= Sheets("Sheet1").Range("B5") And _
                    Cell.Value <= Sheets("Sheet1").Range("C5") Then
                Cell.Interior.ColorIndex = 4
            End If
        Next
        
        For i = 3 To 434
            For Each Cell In Range("H3:H434").Cells
                If Cell.Value < ActiveCell.Offset(i, -1).Value Then
                    Cell.Interior.ColorIndex = 3
                    i = i + 1
                End If
            Next Cell
        Next i
    End Sub
    Last edited by Dragonboy; August 31st, 2012, 12:11. Reason: corrected typo

  • #2
    Re: Conditional formatting based on cell value in an adjacent column using VBA

    Looking at the code where you set range for twocent, did you mean for it to be Range("G3:G434")?

    Code:
     Set twocent = Range("H3:G434")
    Also, you are using the Activecell.Offset and I do not see anywhere in the code where you have selected/activated a cell to make it active.

    Comment


    • #3
      Re: Conditional formatting based on cell value in an adjacent column using VBA

      Sorry. My mistake it was a typo. I fixed it now, but the code still doesn't work.

      Code:
      set onccent = Range("G3:G434")
      set twocent = Range("H3:H434")
      With regards to using Activecell I was thinking that it would happen when the user entered data in to the cell in column H. Should I have used target?

      Comment


      • #4
        Re: Conditional formatting based on cell value in an adjacent column using VBA

        Also, you are using the Activecell.Offset and I do not see anywhere in the code where you have selected/activated a cell to make it active.
        Try this:

        Code:
        Dim address1 As String
        For i = 3 To 434
               For Each Cell In Range("H3:H434").Cells
                 address1 = Cell.address
                    If Cell.Value < Range(address1).Offset(0, -1).Value Then
                       Cell.Interior.ColorIndex = 3
                        i = i + 1
                    End If
                Next Cell
            Next i

        Comment


        • #5
          Re: Conditional formatting based on cell value in an adjacent column using VBA

          Unfortunately it still doesn't work. The cell H3 turns red when there is a figure in cell G3 even though the cell is empty (it should be clear), but as soon as I put a figure in cell H3 that is within the allowed range (but is also different to the figure in cell G3) it turns the cell green.

          Comment


          • #6
            Re: Conditional formatting based on cell value in an adjacent column using VBA

            Sorry, I must be getting sleepy. I'll give it one more try...should have put an "i" in there as opposed to a "0". Hope this takes care of it!
            Code:
                    For Each Cell In Range("G3:G10")
                    address1 = Cell.address
                        If Cell < Range(address1).Offset(0, -1).Value And Cell <> "" Then
                            Cell.Interior.ColorIndex = 3
                            i = i + 1
                            Else
                            Cell.Interior.ColorIndex = 0
                        End If
                        Next Cell

            Comment


            • #7
              Re: Conditional formatting based on cell value in an adjacent column using VBA

              Thanks again for trying to help, but it's still not working. I have uploaded a workbook that might help explain it better than my wording.

              I have also just cross posted this question to try and gain further help.

              Here's the link

              http://www.excelforum.com/excel-prog...using-vba.html
              Attached Files
              Last edited by Dragonboy; September 1st, 2012, 07:19. Reason: Added link to other site

              Comment


              • #8
                Re: Conditional formatting based on cell value in an adjacent column using VBA

                Would something like this be ok ?
                Code:
                For Each Cell In Range("H3:H30").Cells
                    If Cell.Value < Cell.Offset(0, -1).Value Then
                        Cell.Interior.ColorIndex = 6
                    End If
                Next Cell

                Comment


                • #9
                  Re: Conditional formatting based on cell value in an adjacent column using VBA

                  What are the conditions?

                  It's not particularly clear which cells should be formatted when.

                  For example, should both cells be formatted red if they aren't equal?

                  That's what this code does.
                  Code:
                  Option Explicit
                  Private Sub Worksheet_Change(ByVal Target As Range)
                  
                  Dim onecent As Range
                  Dim twocent As Range
                  Dim Cell
                  
                      Set onecent = Range("G3:G30")
                  
                  
                      For Each Cell In col.Cells
                          If Cell.Value = "" Then
                              Cell.Interior.ColorIndex = 2
                          End If
                  
                          If Cell.Value > 0 And Cell.Value < Range("M4") Then
                              Cell.Interior.ColorIndex = 3
                          End If
                  
                          If Cell.Value > Range("N4") Then
                              Cell.Interior.ColorIndex = 3
                          End If
                  
                          If Cell.Value >= Range("M4") And _
                             Cell.Value <= Range("N4") Then
                              Cell.Interior.ColorIndex = 4
                          End If
                      Next
                  
                  
                      Set twocent = Range("H3:H30")
                  
                      For Each Cell In twocent
                          If Cell.Value = "" Then
                              Cell.Interior.ColorIndex = 2
                          End If
                  
                          If Cell.Value > 0 And Cell.Value < Range("M5") Then
                              Cell.Interior.ColorIndex = 3
                          End If
                  
                          If Cell.Value > Range("N5") Then
                              Cell.Interior.ColorIndex = 3
                          End If
                  
                          If Cell.Value >= Range("M5") And _
                             Cell.Value <= Range("N5") Then
                              Cell.Interior.ColorIndex = 4
                          End If
                      Next
                  
                  
                      For Each Cell In Range("G3:G30").Cells
                          If Cell.Value <> Cell.Offset(, 1).Value Then
                              Cell.Resize(, 2).Interior.ColorIndex = 3
                          End If
                      Next Cell
                  
                  End Sub
                  Boo!

                  Comment


                  • #10
                    Re: Conditional formatting based on cell value in an adjacent column using VBA

                    My aplologies. I will try to be clearer.

                    In range onecent ("G3:G30") there will be percentages entered by a user and these will be conditionally formatted based upon if they are between the range M4(min) and N4 (max) If the value in range oncecent is in this range the cell should be colored green. If it is not within this range the cell should be colored red.
                    Now when a user enters a percentage in range twocent ("H3:H30") the value must be between range M5 (min) and N5 (max). If the value is within this range then the cell should be colored green, however if the value in column H is different to column G, but the same row, but it still within the specified range the cell should be colored red.

                    e.g. G3 value is 85% H3 value is 86% then G3 should be green, but H3 should be red even though it is within the allowed range.

                    Edit: I adapted your code norie using rollis13's suggestion and the below works out for the moment.
                    Thank you both

                    Code:
                    Option Explicit
                    Private Sub Worksheet_Change(ByVal Target As Range)
                         
                        Dim onecent As Range
                        Dim twocent As Range
                        Dim Cell
                    
                        
                        
                        Set onecent = Range("G3:G30")
                             
                        For Each Cell In onecent
                            If Cell.Value = "" Then
                                Cell.Interior.ColorIndex = 2
                            End If
                             
                            If Cell.Value > 0 And Cell.Value < Range("M4") Then
                                Cell.Interior.ColorIndex = 3
                            End If
                             
                            If Cell.Value > Range("N4") Then
                                Cell.Interior.ColorIndex = 3
                            End If
                             
                            If Cell.Value >= Range("M4") And _
                            Cell.Value <= Range("N4") Then
                                Cell.Interior.ColorIndex = 4
                            End If
                        Next
                         
                         
                        Set twocent = Range("H3:H30")
                         
                        For Each Cell In twocent
                            If Cell.Value = "" Then
                                Cell.Interior.ColorIndex = 2
                            End If
                             
                            If Cell.Value > 0 And Cell.Value < Range("M5") Then
                                Cell.Interior.ColorIndex = 3
                            End If
                             
                            If Cell.Value > Range("N5") Then
                                Cell.Interior.ColorIndex = 3
                            End If
                             
                            If Cell.Value >= Range("M5") And _
                            Cell.Value <= Range("N5") Then
                                Cell.Interior.ColorIndex = 4
                            End If
                        Next
                         
                         
                        For Each Cell In Range("H3:H30").Cells
                            If Cell.Value <> Cell.Offset(, -1).Value And Cell.Value > 0 Then
                                Cell.Interior.ColorIndex = 3
                            End If
                        Next Cell
                         
                    End Sub
                    Last edited by Dragonboy; September 1st, 2012, 10:42. Reason: Solved

                    Comment

                    Working...
                    X