I am trying to highlight certain cells based on it's column 4 values.
Basically, If column 4 value is negative, then we search through the column 4 from the beginning and if there is positive match (That is positive of the column 4 value and then matching the respective column 3 and 6 values) then highlight all those together.
Additionally, once paired and highlighted, it should not be picked again in the middle loop therefore another condition added there.
This code is doing my job to an extent. But in between it misses some negative values that can match against the positives. Any help would be greatly appreciated
Basically, If column 4 value is negative, then we search through the column 4 from the beginning and if there is positive match (That is positive of the column 4 value and then matching the respective column 3 and 6 values) then highlight all those together.
Additionally, once paired and highlighted, it should not be picked again in the middle loop therefore another condition added there.
This code is doing my job to an extent. But in between it misses some negative values that can match against the positives. Any help would be greatly appreciated
Code:
Sub Button1_Click()
Dim rownumber As Integer
Dim ColumnC, ColumnF, ColumnC1, ColumnF1 As String
Dim ColumnD, ColumnD1 As Integer
Dim subrownumber As Integer
Dim Condition As Boolean
rownumber = 1
Do
ColumnD = Cells(rownumber, 4).Value
ColumnC = Cells(rownumber, 3).Value
ColumnF = Cells(rownumber, 6).Value
Condition = False
If (ColumnD < 0) Then
subrownumber = 1
Do
ColumnD1 = Cells(subrownumber, 4).Value
ColumnC1 = Cells(subrownumber, 3).Value
ColumnF1 = Cells(subrownumber, 6).Value
If (ColumnD1 = ColumnD * -1 And ColumnF1 = ColumnF And ColumnC1 = ColumnC) And _
Cells(subrownumber, 4).Interior.ColorIndex <> 37 Then
' Cells(rownumber, 4).Interior.ColorIndex <> 37
Cells(subrownumber, 4).Interior.ColorIndex = 37
Cells(subrownumber, 3).Interior.ColorIndex = 37
Cells(subrownumber, 6).Interior.ColorIndex = 37
Cells(rownumber, 4).Interior.ColorIndex = 37
Cells(rownumber, 3).Interior.ColorIndex = 37
Cells(rownumber, 6).Interior.ColorIndex = 37
Condition = True
End If
subrownumber = subrownumber + 1
Loop Until IsEmpty(Cells(subrownumber, 4)) Or Condition = True
End If
rownumber = rownumber + 1
Loop Until IsEmpty(Cells(rownumber, 4))
End Sub
Last edited: