Coloring multiple rows

[Anders.Hoejlund]

Hello

I have nearly the perfect macro for my job, but I need one little change. The thing I is that my macro should color the row within the search conditions is and the two rows below. At this moment it only colors the row with the search result.

Heres my macro:

Sub Farv_weekend()

    Dim LRow As Integer
    Dim LCell As String
    Dim LColorCells As String
    'Start at row 4
    LRow = 4

    'Update row colors for the first 150 rows
    While LRow < 150
        LCell = "A" & LRow
        'Color will changed in columns A to G
        LColorCells = "A" & LRow & ":" & "G" & LRow

        Select Case Left(Range(LCell).Value, 6)

            'Set row color to light blue
            Case "Lørdag"
                Range(LColorCells).Interior.ColorIndex = 15
                Range(LColorCells).Interior.Pattern = xlSolid

            'Set row color to light green
            Case "Søndag"
                Rows(LRow & ":" & LRow).Select
                Range(LColorCells).Interior.ColorIndex = 15
                Range(LColorCells).Interior.Pattern = xlSolid

            'Default all other rows to no color
            Case Else
                Rows(LRow & ":" & LRow).Select
                Range(LColorCells).Interior.ColorIndex = xlNone

        End Select

        LRow = LRow + 1
    Wend

    Range("A1").Select

End Sub

Looking forward to get some help

Anders
Denmark

[snb]

Sub snb()
    Range("A4:G150").Interior.ColorIndex = xlNone
    Range(Join(Filter([transpose(if(left(A4:A150,6)="Lørdag","A" & row(4:150)& ":G" & row(4:150),""))], "A"), ",")).Interior.ColorIndex = 15
    Range(Join(Filter([transpose(if(left(A4:A150,6)="Søndag","A" & row(4:150)& ":G" & row(4:150),""))], "A"), ",")).Interior.ColorIndex = 16
End Sub

[JBeaucaire]

The smallest change I can think of would be to just add this:

        LColorCells = "A" & LRow & ":" & "G" & LRow + 2

SNB, yours is intriguing but not working for me. I get error messages when I insert that into a module.

[snb]

@JBeaucaire

See the attachment.

[JBeaucaire]

Very intriguing, something new to pull apart.

[snb]

@JBeaucaire

To provide you with another example, using the same techniques for quite another purpose, look here.

[JBeaucaire]

Yeah, I'd seen that before. I have a rule against variables that give no indication what they do, code is hard enough to read without intentionally creating variables that make it significantly harder to decipher, I think we've talked about this briefly in the past.

I know that means the code will be longer, but longer and legible wins for me from a teaching standpoint.

The technique is pretty advanced as it is...

[Anders.Hoejlund]

Thank you all very much, I inserted + 2 and deleted the part of the code wich gave default no color, this kindly deleted the coloring of the two rows below, just after they were colored. It works now.

Have a nice day

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.