I have no idea why macro highlighting wrong rows

[anand3dinesh]

Hi Excel Experts,

i have this code to highlight holidays in my daily tracker. when i run this code it highlights wrong rows also wrong cells.
Please check my code below. also image attached to show what cells exactly code is highlighting when i run this.
Many Thanks.


Sub holidays()

Dim i As Long
Dim lstrow As Long

lstrow = Range("B100").End(xlUp).Row
For i = 1 To lstrow
On Error Resume Next
Cells(i, 3).Find("Saturday").Range("A" & i & ":" & "E" & i).Interior.Color = RGB(248, 203, 173)
Cells(i, 3).Find("Sunday").Range("A" & i & ":" & "E" & i).Interior.Color = RGB(248, 203, 173)

Next i

End Sub

 

[aRandomHelper]

If you meant holidays = Saturdays and Sundays, maybe try this:

Sub holidays()

Dim i As Long
Dim lstrow As Long

lstrow = Range("B100").End(xlUp).Row
For i = 1 To lstrow
    If Cells(i, 3).Value = "Saturday" Or Cells(i, 3).Value = "Sunday" Then Range("A" & i & ":" & "E" & i).Interior.Color = RGB(248, 203, 173)
Next i
End Sub

 

[footoo]

Because this : .Range("A" & i & ":" & "E" & i)

Is offsetting Cells(i, 3).Find("Saturday") by i rows.

(Are you sure the code highlights A:E and not C:G ?)

Try this :

Cells(i, 3).Find("Saturday").Offset(0, -2).Resize(, 5).Interior.Color = RGB(248, 203, 173)

Your code is not very efficient.
Why not try conditional formatting instead.

 

[anand3dinesh]

If you meant holidays = Saturdays and Sundays, maybe try this:

Sub holidays()

Dim i As Long
Dim lstrow As Long

lstrow = Range("B100").End(xlUp).Row
For i = 1 To lstrow
    If Cells(i, 3).Value = "Saturday" Or Cells(i, 3).Value = "Sunday" Then Range("A" & i & ":" & "E" & i).Interior.Color = RGB(248, 203, 173)
Next i
End Sub

This works perfect thank you

 

[anand3dinesh]

Because this : .Range("A" & i & ":" & "E" & i)

Is offsetting Cells(i, 3).Find("Saturday") by i rows.

(Are you sure the code highlights A:E and not C:G ?)

Try this :

Cells(i, 3).Find("Saturday").Offset(0, -2).Resize(, 5).Interior.Color = RGB(248, 203, 173)

Your code is not very efficient.
Why not try conditional formatting instead.

i tried conditional formating but i can only able to highlight perticular cell, i want to hilight row from A:E

 

[footoo]

i tried it but i can only able to highlight perticular cell, i want to hilight row from A:E

If you mean conditional formatting :
• Select columns A:E
• CF formula : =OR($C1="Saturday",$C1="Sunday")

 

[anand3dinesh]

If you mean conditional formatting :
• Select columns A:E
• CF formula : =OR($C1="Saturday",$C1="Sunday")

This works perfect Thanks