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:
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.
CodePudding user response:
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