what's wrong with this code?

[abdelfattah]

hello
i try writing code in earlier time it was working but now it works only to specific row 2,5 my idea when the value "paid" is existed in column a then should highlight the cells in columns I,J,K
as in my picture in row6 should be color because column e contains "paid"


this is my code

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("e:e")) Is Nothing Then
Dim i As Long, r1 As Range, r2 As Range
lr = Cells(Rows.Count, 1).End(xlUp).Row + 1
   For i = 2 To lr
      Set r1 = Range("e" & i)
      Set r2 = Range("i" & i & ":k" & i)
      If r1.Value = "paid" Then
      r2.Interior.color = vbBlue
      Else
      r2.Interior.ColorIndex = xlNone
      End If
   Next i
   End If
End Sub

color (1).xlsm
	E	F	G	H	I	J	K
1	case				debit	credit	balance
2	paid				1000	200	800
3	unpaid				1000		1000
4	paid				2000	500	1500
5	unpaid				1020		1020
6	paid				1200	200	1000
13

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E4	Cell Value	=1	text	NO
C1:E1	Cell Value	=1	text	NO
C2:C3,E6,E2:E3	Cell Value	=1	text	NO

 

[MARK858]

What row does your data in column A go down to?

 

[mohadin]

Hi
Try

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("e:e")) Is Nothing Then
        If Target.Value = "paid" Then
            Target.Offset(, 4).Resize(, 3).Interior.Color = vbBlue
        Else
            Target.Offset(, 4).Resize(, 3).Interior.ColorIndex = xlNone
        End If
    End If
End Sub

 

[footoo]

mohadin
Your code does not provide for the possibility of the target being more than one cell.

 

[abdelfattah]

@mark if you mean beginning from a2

 

[MARK858]

Your code is based on the last used row in column A change the 1 below to a 5 so it is based on column E.

lr = Cells(Rows.Count, 1).End(xlUp).Row + 1

 

[abdelfattah]

you're right @mark many thanks for your assistance

 

[MARK858]

You're welcome

 

[abdelfattah]

thanks @ Mohadin I tested your code is awesome

 

[footoo]

thanks @ Mohadin I tested your code is awesome

Try inserting or deleting a row.