How to fix the error in the excel vba code?

Question

I want to have alternate backgrd colour for different text

I wrote a code for it and there are several errors. How can I improve it? Thanks

Sub Alternatecolour()
    Flag = True
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    Startcl = Cells(2, "D")

    For Each cl In Range("D2:D" & lr)

    str1 = cl.Text
    str2 = cl.Offset(-1, 0).Text

    Diff = StrComp(str1, str2, vbBinaryCompare)

    If Diff = 0 Then
    GoTo Loopend
    End If

    If Diff <> 0 Then

        If Flag = True Then
        Range(Startcl, cl).Interior.Color = 15
        Startcl = cl
        Flag = False
        Else
        Range(Startcl, cl).Interior.Color = 16
        Startcl = cl
        Flag = True

        End If

    End If

    Loopend
    Next cl
End Sub

One thing caught my eye... Loopend before Next cl shoud be Loopend: — Siddharth Rout
– Siddharth Rout, Commented Mar 28, 2019 at 11:24
Sounds like you could do this with just Excel Conditional Formatting as an alternative. — Andy G
– Andy G, Commented Mar 28, 2019 at 11:30
Also, you could easily rewrite this without GoTo and I encourage you to do so. — Andy G
– Andy G, Commented Mar 28, 2019 at 11:31

Pᴇʜ · Accepted Answer · 2019-03-28 11:27:30Z

3

I suggest the following code:

Public Sub AlternateColor()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("ColorMe")

    Dim ColorRange As Range
    Set ColorRange = ws.Range("D2", ws.Cells(ws.Rows.Count, "D").End(xlUp))

    Dim StartRow As Long
    StartRow = ColorRange.Row

    Dim ActColor As Long
    ActColor = 15

    Dim iRow As Long
    For iRow = ColorRange.Row To ColorRange.Rows.Count + ColorRange.Row - 1
        If ws.Cells(iRow, "D").Value <> ws.Cells(iRow, "D").Offset(1, 0).Value Then
            ws.Range(ws.Cells(StartRow, "D"), ws.Cells(iRow, "D")).Interior.ColorIndex = ActColor
            ActColor = IIf(ActColor = 15, 16, 15)
            StartRow = iRow + 1
        End If
    Next iRow
End Sub

answered Mar 28, 2019 at 11:27

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to fix the error in the excel vba code?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related