Color Range in row

[Belleke]

Who can help me a bit further
I have this code

Private Sub Worksheet_Change(ByVal Target As Range)
lRow = Range("P" & Rows.Count).End(xlUp).Row
Set MR = Range("P" & lRow)
For Each cell In MR
If cell.Value = "1" Then cell.Interior.ColorIndex = 43
If cell.Value = "" Then cell.Interior.ColorIndex = xlNone
Next
End Sub

But If in Column P the value is 1 It should color the row from A to Q
If the value is not 1 then no color
I am stuck, pls help
Example included

 

[Chihiro]

Something like this?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range, MR As Range
Dim lRow As Long
If Not Intersect(Target, Range("P:P")) Is Nothing Then
    lRow = Range("P" & Rows.Count).End(xlUp).Row
    Set MR = Range("P1:P" & lRow)
    For Each cell In MR
        If cell.Value = "1" Then cell.Offset(, -15).Resize(, 17).Interior.ColorIndex = 43
        If cell.Value <> "1" Then cell.Offset(, -15).Resize(, 17).Interior.ColorIndex = xlNone
    Next
End If
End Sub

 

[vletm]

MR = Range("P" & lRow) ... means one cell
ForEach cell In MR ... still one cell
>>> Did You get an idea or the reason?

 

[Belleke]

Hello Chihiro,
Thank you for your time and efford
The code works fine
Just need a little extra, the cells should decolor if the cell is empty
Something in that direction

If IsEmpty(cell.Value) Then cell.Offset(, -15).Resize(, 17).Interior.ColorIndex = xlNone

I tryed different pieces of code but nothing seems to work
Please advice
Thanks

 

[Chihiro]

Hm? If you look at the one I posted earlier. It handles blanks. <>"1" is checking for that and works with formula generated blanks. Where as IsEmpty will not.

 

[Belleke]

Hoi Chihiro,
No Go
File included

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[Belleke]

Thank you Narayank991,
Thats exactly where I was looking for.
Thank you.