Results 1 to 5 of 5

053011, 13:44 #1Registered User
 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 10005000 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
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

053011, 13:57 #2Registered User
 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

053011, 17:19 #3Registered User
 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
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
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

053111, 13:09 #4Registered User
 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 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 100010,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

060311, 10:28 #5Registered User
 Join Date
 Sep 2006
 Location
 Surrey, UK
 Posts
 994
Provided Answers: 2Your 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 noone else does. The rest is misdirection.