Excel BA loop correction - Excel VBA

Isu_Wic

New Member
Joined
Sep 16, 2015
Messages
1
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


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:

Forum statistics

Threads
1,085,587
Messages
5,384,610
Members
401,913
Latest member
chethan av

Some videos you may like

This Week's Hot Topics

Top