Search code examples
excelvbaconditional-formatting

VBA Compare single row values and highlight the entire row if different

My code uses conditional formatting to look at the row values in Column A "Order ID", compares them, and then formats the cell if the row values are different. Instead of formatting the cell, how do I format the entire row based off of consecutive row values in Column A "Order ID" being different?

Said differently - if the value in Column A "Order ID" is different from the previous value in Column A "Order ID", I want to format the entire row that is different. My data is variable everyday so I need to use VBA!

Here is the output of my current code: enter image description here

This is the desired outcome: enter image description here

Here is the code

Sub Fulfillment()
'
' Fulfillment Macro
' Format the order number in column A as plum


Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=MOD(SUM((A$2:A2<>A$1:A1)*1),2)=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font.Color = RGB(0, 0, 0)
    
End With
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = RGB(221, 160, 221)
    .TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

Application.DisplayAlerts = True 
Application.ScreenUpdating = True
End Sub

Thank you! I do not necessarily need a conditional formatting solution, just a VBA solution that works dynamically.

Solution

  • A Different Flavor of Banded Rows

    Option Explicit

Sub Fulfillment()
'
' Fulfillment Macro
' Format the order number in column A as plum

    Const CriteriaColumn As Long = 1

    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1") ' adjust
    
    Dim rg As Range: Set rg = ws.Range("A1").CurrentRegion
    
    Set rg = rg.Resize(rg.Rows.Count - 2).Offset(2) ' exclude first two rows
    
    Application.ScreenUpdating = False
    
    rg.Interior.Color = xlNone
    
    Dim Col As Long: Col = 1
    
    Dim cell As Range
    Dim r As Long
    
    For Each cell In rg.Columns(CriteriaColumn).Cells
        r = r + 1
        If cell.Value <> cell.Offset(-1).Value Then Col = Col Mod 2 + 1
        If Col = 2 Then rg.Rows(r).Interior.Color = RGB(221, 160, 221)
    Next cell
    
    Application.ScreenUpdating = True
    
    MsgBox "Fulfillment accomplished.", vbInformation

End Sub