Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

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

  1. #1
    Join Date
    31st August 2012
    Posts
    15

    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
    VB:
    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 at 12:11. Reason: corrected typo

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    3rd September 2010
    Posts
    62

    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")?

    VB:
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    31st August 2012
    Posts
    15

    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.

    VB:
    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?

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    3rd September 2010
    Posts
    62

    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:

    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    31st August 2012
    Posts
    15

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    3rd September 2010
    Posts
    62

    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!
    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    31st August 2012
    Posts
    15

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by Dragonboy; September 1st, 2012 at 07:19. Reason: Added link to other site

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    30th September 2010
    Location
    Cordenons
    Posts
    219

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

    Would something like this be ok ?
    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    14th July 2004
    Posts
    10,539

    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.
    VB:
    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!

  10. #10
    Join Date
    31st August 2012
    Posts
    15

    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

    VB:
    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 at 10:42. Reason: Solved

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 1
    Last Post: June 13th, 2012, 08:43
  2. Replies: 4
    Last Post: October 16th, 2011, 03:39
  3. Replies: 1
    Last Post: July 4th, 2011, 06:08
  4. Conditional formatting in an adjacent cell
    By jledlow in forum EXCEL HELP
    Replies: 3
    Last Post: February 17th, 2006, 12:51
  5. Replies: 5
    Last Post: August 19th, 2003, 20:20

Bookmarks

Posting Permissions

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