Excel VBA

css navigation by Css3Menu.com

Compare 2 Columns

This client wanted some “Conditional Formatting” to compare adjacent cells. I couldn't figure how to do that so wrote a quick macro.
Sub ColorOverWorked()
    Dim DNrow       As Long
    Dim LastRow     As Long
    Dim CurrRow     As Long
    Dim LastCol     As Long
    Dim CurCol      As Long
    Dim L           As Long
    LastRow = Range("A1000000").End(xlUp).Row   'Find last row
    LastCol = Range("XFD4").End(xlToLeft).Column    'Get last used column
    DNrow = 0       'Instances counter
    CurrRow = 4     'First row of data
    For L = CurrRow To LastRow
        For CurCol = 3 To LastCol Step 2    'Choose next D column
            Select Case Cells(L, CurCol - 1).Value  'look at previous colimn entry
                Case 1
                    If Cells(L, CurCol).Value = 1 Then  'if both are 1
                       Cells(L, CurCol).Interior.Color = RGB(254, 0, 0) 'set cell color
                       DNrow = DNrow + 1    'increment found counter
                    End If
                Case 0      'if zero do nothing at this time
            End Select

        Next CurCol
       
    Next L
    MsgBox "Completed checking for Night & Day and found " & DNrow & " instances ", vbInformation, "Completed Run"
End Sub

We added a button to the spreadsheet so they can run the procedure everyday if desired.

© 2011-2024

Updated:  01/23/2024 13:34
This page added:  11 May 2011