CODEWITHSUNDEEP
vbaexcel
Bharath Babu
Bharath Babu

Reputation: 65

How To Write Macro for highlight filtered rows alternatively

How to Write Macro for this structure of Formation in Excel To Highlight Filtered rows alternatively.. Thanks in Advance

enter image description here

Upvotes: 1

Views: 691

Answers (3)

Amorpheuses
Amorpheuses

Reputation: 1423

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'd like to color the whole row you can replace the If colorCell statement in the code above with this:

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

Upvotes: 0

user3598756
user3598756

Reputation: 29421

if your numbers are divided into chunks of always different numbers repetitions, then you could 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

a Conditional formatting approach is possible with a helper column

assuming:

  • your data are in column A, beginning from row 2

  • column B is free

then:

  • write the following formula in helper column B cells:

    =IF(A2<>A1,B1+1,0)

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

    =INT(B2/2)=B2/2

and choosing the format you like to highlight cells

Upvotes: 1

S Meaden
S Meaden

Reputation: 8270

Here you are friend, replace Sheet4 with 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

Upvotes: 1

Related Questions