Suppose I have a column of numbers, for example:
1; -1; 5; 4; 3; -3; -3; 3; -4; 7.
I want to make a macro that can highlight all pairs of positive and negative numbers (ex. 1 and -1) while also taking in consideration the fact that multiple pairs can appear (ex. 3 and -3 both appear twice). Also, I want to be able to input the range that I want to work with.
For the above example, all numbers should be highlighted except 5 and 7.
Here's what I came up with so far
Sub HighlightExercise()
Set myRange = Application.InputBox(prompt:="Sample", Type:=8)
myRange.Interior.ColorIndex = 2
For Each cell In myRange
If cell.Interior.ColorIndex = 30 Then Next
Set CValue = Cell.Value
myRange.Select
Set CFind = Selection.Find(What:=CValue.Value * -1, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False)
If CFind.Value = Null Then Next
If CFind.Interior.ColorIndex = 30 Then Next
CFind.Interior.ColorIndex = 30
CValue.Interior.ColorIndex = 30
Next
End Sub
In the above example, it says "Compile error, Next without For" yet the For condition is there. I tried with "Next Cell" and "Next iteration" but still nothing. What am I not getting?
Instead of:
If cell.Interior.ColorIndex = 30 Then Next
You need to test for the opposite and allow it to run the code if true:
If cell.Interior.ColorIndex <> 30 Then
Also you use .Value
in many places that does not allow it:
Set CValue = Cell.Value
Should be:
Set CValue = Cell
But really that is not needed as Cell
is already a range.
Don't forget to declare your variables:
Dim myRange As Range
Dim cell As Range
Dim cfind As Range
Also in the Find, we want to search from the current cell down so change:
After:=ActiveCell
To
After:=cell
Avoid using .Select
just do what you want with the range:
Set cfind = myRange.Find...
try:
Sub HighlightExercise()
Dim myRange As Range
Dim cell As Range
Dim cfind As Range
Set myRange = Application.InputBox(prompt:="Sample", Type:=8)
myRange.Interior.ColorIndex = 2
For Each cell In myRange
If cell.Interior.ColorIndex <> 30 Then
Set cfind = myRange.Find(What:=cell.Value * -1, After:=cell, LookIn:= _
xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False)
If Not cfind Is Nothing Then
If cfind.Interior.ColorIndex <> 30 Then
cfind.Interior.ColorIndex = 30
cell.Interior.ColorIndex = 30
End If
End If
End If
Next