How to write a macro to highlight filtered strings as an alternative

Question

How to write a macro for this Formation structure in Excel to highlight filtered rows as an alternative .. Thanks in Advance

Answer 1

if your numbers are divided into pieces of repeating repetitions of different numbers, you can use this VBA code:

Sub main() Dim item As Variant Dim startRow As Long Dim okHighlight As Boolean With Range("A1", Cells(Rows.count, 1).End(xlUp)) For Each item In GetUniqueValues(.Cells).Items If okHighlight Then .Range(.Cells(startRow, 1), .Cells(item, 1)).Interior.ColorIndex = 48 startRow = item + 1 okHighlight = Not okHighlight Next End With End Sub Function GetUniqueValues(rng As Range) As Dictionary Dim cell As Range Dim dict As Dictionary Set dict = New Dictionary With dict For Each cell In rng .item(cell.Value) = cell.row - rng.Rows(1).row + 1 Next End With Set GetUniqueValues = dict End Function 

---

possible conditional formatting approach with auxiliary column

on condition:

• your data is in column A starting at row 2

• column B is free

then

• write the following formula in the cells of auxiliary column B:

  = IF (A2 <> A1, B1 + 1.0)

• apply conditional formatting to column A with the following formula:

  = INT (B2 / 2) = B2 / 2

and selecting the format you want to highlight with cells

Answer 2

Here you are a friend, replace Sheet4 with the name of your sheet.

 Option Explicit Sub Test() Dim rngOrigin As Excel.Range Set rngOrigin = Sheet4.Cells(1, 1) Dim rng As Excel.Range Set rng = Sheet4.Range(rngOrigin, rngOrigin.End(xlDown)) Dim bToggle As Boolean Dim rngLoop As Excel.Range For Each rngLoop In rng If rngLoop.Row > 1 Then If rngLoop.Offset(-1, 0).Value <> rngLoop.Value Then bToggle = Not bToggle End If End If rngLoop.Interior.ColorIndex = VBA.IIf(bToggle, 4, 2) Next End Sub 

Answer 3

Among the many ways to do this, here's another:

 Option Explicit Sub colorAltRowGroups() With Sheets(1) Dim colorCell As Boolean: colorCell = False Dim val As String, prvVal As String prvVal = .Cells(1, 1).Value Dim c As Range For Each c In Range("A1", .Cells(Rows.Count, 1).End(xlUp)): val = c.Value If (val <> prvVal) Then colorCell = Not colorCell If colorCell Then c.Interior.Color = vbYellow prvVal = val Next End With End Sub 

EDIT:

If you want to color the entire line, you can replace the If colorCell statement in the above code with the following:

 If colorCell Then c.EntireRow.Interior.Color = vbYellow