highlighting macro in Excel VBA help

sinamon · #1 (**permalink**) 05-30-11, 13:44

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

sinamon · #2 (**permalink**) 05-30-11, 13:57

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

Colin Legg · #3 (**permalink**) 05-30-11, 17:19

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

sinamon · #4 (**permalink**) 05-31-11, 13:09

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

weejas · #5 (**permalink**) 06-03-11, 10:28

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech