Results 1 to 5 of 5
  1. #1
    Join Date
    May 2011
    Posts
    3

    Unanswered: highlighting macro in Excel VBA help

    need help with highlighting macro in Excel VBA
    code finds values in specified range 1000-5000 and highlights
    the adjacent cell if criteria is met .
    Here the code that i got working but I have
    a two part problem with it.

    Code:
    Sub Highlight()
    Dim rValues As Range
        Set rValues = ThisWorkbook.Sheets(1).Range("N5:N200")
    For Each cell In Range("N5:N200")
         
    If cell.Value >= 1000 And cell.Value <= 5000 Then
             
            cell.Offset(0, 1).Interior.ColorIndex = 37
         
        End If
    Next cell
    
    End Sub
    Problem 1
    It highlights but it stops at row 37 for some reason even though there are more cells to highlight after .

    Problem 2
    I need to add another range with another color but I get the next without for error
    Code:
    Dim rValues As Range
        Set rValues = ThisWorkbook.Sheets(1).Range("N5:N200")
    For Each cell In Range("N5:N200")
         
    If cell.Value >= 1000 And cell.Value <= 5000 Then
             
            cell.Offset(0, 1).Interior.ColorIndex = 37
         
         
    If cell.Value >= 5000 And cell.Value <= 100000 Then
             
            cell.Offset(0, 1).Interior.ColorIndex = 22
        End If
    Next cell
    
            
    End Sub

  2. #2
    Join Date
    May 2011
    Posts
    3

    problem 1 solved help with problem 2

    Ok I solved the first problem, turns out it stopped highlighting because
    one of the cells value was text and not numeric. So I solved problem
    1. If I can get help with problem 2
    next without for error would be great.
    Thanks

  3. #3
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi and welcome to the forum.

    If you indent your code the problem becomes a little clearer. If you were to keep your current structure, you are missing an End If:

    Code:
    Sub Higlight()
        Dim rValues As Range
     
        Set rValues = ThisWorkbook.Sheets(1).Range("N5:N200")
     
        For Each cell In Range("N5:N200")
            If cell.Value >= 1000 And cell.Value <= 5000 Then
                cell.Offset(0, 1).Interior.ColorIndex = 37
     
                If cell.Value >= 5000 And cell.Value <= 100000 Then
                    cell.Offset(0, 1).Interior.ColorIndex = 22
                End If
            End If 
        Next cell
     
    End Sub
    However, the logic is wrong. Rather than a second If...End If block, you need an If...ElseIf...End If block.
    Code:
    Sub Higlight()
        Dim rValues As Range
     
        Set rValues = ThisWorkbook.Sheets(1).Range("N5:N200")
     
        For Each cell In Range("N5:N200")
            If cell.Value >= 1000 And cell.Value <= 5000 Then
                cell.Offset(0, 1).Interior.ColorIndex = 37
     
            ElseIf cell.Value >= 5000 And cell.Value <= 100000 Then
                cell.Offset(0, 1).Interior.ColorIndex = 22
     
            End If
        Next cell
     
    End Sub
    I assume you know that this can be done without VBA (ie. conditional formatting).

    At the moment, your cell variable is not declared and your rValues variable is not being used. Accounting for these, the code then becomes:
    Code:
    Sub Higlight()
        Dim rValues As Range
        Dim cell As Range
     
        Set rValues = ThisWorkbook.Sheets(1).Range("N5:N200")
     
        For Each cell In rValues.Cells
            If cell.Value >= 1000 And cell.Value <= 5000 Then
                cell.Offset(0, 1).Interior.ColorIndex = 37
     
            ElseIf cell.Value >= 5000 And cell.Value <= 100000 Then
                cell.Offset(0, 1).Interior.ColorIndex = 22
     
            End If
        Next cell
     
    End Sub

  4. #4
    Join Date
    May 2011
    Posts
    3

    Its highlighting but it seems its not highlighting based on what im telling it.

    hi thanks for the welcome, Im totally new to Excel so I was not aware
    of conditional formating but im reading up on it, And I'm looking forward to learn everything I can here.
    As far as my problem im still stuck, Its highlighting but it seems
    its not highlighting based on what im telling it.
    here what I have now this is just the first parameter, again its highlighting
    but not correctly
    Code:
    Sub highlight()
    For Each cell In Range("B2:B200")
         
    If cell.Value >= 1000 And cell.Value <= 10000 Then
             
            cell.Offset(2, -1).Interior.ColorIndex = 6
        End If
    Next cell
    End Sub
    heres the sample file im testing onhighlightsample.zip

    heres what I'm trying to accomplish.
    I want cells with text in A2:A200 to highlight based on values in col B and Col C

    if col B is 1000-10,000 highlight yellow

    if col B is 10,000 or more highlight green

    if col B is 5000 or more and Col C is less than 20000 highlight red.

    all this is activated by a command button

    this is a keyword volume and competition analyzer just so you know
    what Im doing.
    Hope you can help thanks

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Your conditions are not mutually exclusive. For example, if the cell in col B is 10,000, do you want the cell in col A to light up yellow or green? Therefore you need to work out a priority order for them before you can code the highlighting.

    However, as you have three conditions, you can use conditional formatting. What follows is my best guess for what you want to achieve:
    • Select cell A2
    • Open the conditional formatting menu (Format -> Conditional Formatting...)
    • Click the "Add" button twice to display all three conditions.
    • For condition 1, change "Cell Value Is" to "Formula Is"
    • In the next box, enter "=AND(B2 >= 5000, C2 < 20000)"
    • Click the "Format..." button to open the formatting palette and select the "Patterns" tab
    • Set the appropriate highlighting and click "OK"
    • For condition 2, change "Cell Value Is" to "Formula Is"
    • In the next box, enter "=AND(B2 >= 1000, B2 <= 10000)"
    • Set the required formatting
    • For condition 3, select "Formula Is" and in the next box enter "=AND(B2 > 10000)", then set its formatting

    You can then use the Format Copy function to copy the conditional formatting down to all the required cells in col A.

    What this will give you:
    • For any rows in which col B is at least 5000 and col C is less that 20,000, col A will be red
    • For any rows in which col B is between 1000 and 10,000, col A will be yellow
    • For any rows in which col B is greater than 10,000, col A will be green

    Conditional formatting will only apply one format to a cell, and applies the first one in the list that matches. If you find that some cells in col A are appearing red when you think that they should be yellow or green, move the conditions around.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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