Change cell value if adjacent cell is not blank

[chris_huh]

I need cells C8:C15 to be set to "No" or blank if the adjacent cell in column B is either populated or empty, respectively.

I have:

Dim LR As Long, i As Long
LR = Range("B8:B15").Row
For i = 8 To LR
With Range("B" & i)
If Value = "" Then
.Offset(, 1).Value = ""
Else
.Offset(, 1).Value = "No"
End If
End With
Next i

but it doesn't work properly.

What have i done wrong?

 

[VoG]

Try

Dim LR As Long, i As Long
LR = Range("B" & rows.Count).End(xlUp).Row
For i = 8 To LR
    With Range("B" & i)
        If .Value = "" Then
            .Offset(, 1).Value = ""
        Else
            .Offset(, 1).Value = "No"
        End If
    End With
Next i

 

[chris_huh]

It looked like that was working to start with, but i need it to only work on rows 8-15. I have other text in the lower down cells which can't be changed.

 

[VoG]

Try

Dim i As Long
For i = 8 To 15
    With Range("B" & i)
        If .Value = "" Then
            .Offset(, 1).Value = ""
        Else
            .Offset(, 1).Value = "No"
        End If
    End With
Next i

 

[chris_huh]

Great, that works perfectly.

thanks