VBA If's on multiple columns

[stevenm]

Hi All,

Trying to write a VBA macro that runs down column A and if it has a value then checks to see if column B has content and if it doesn't highlights the cell in B a color.

Below is my example:

Excel 2010
	A	B
1	Value 1	Value 2
2	abc123	xyz
3	abc124	xyz
4	abc125	xyz
5	abc126
6	abc127	xyz
7	abc128	xyz
8	abc129
9	abc130	xyz
10	abc131	xyz
11	abc132	xyz
12	abc133	xyz
13	abc134	xyz
14	abc135
Sheet1

Ideally Id like the result of running the macro to be that cells B5, B8 and B14 all become highlighted in orange.

Any help is appreciated.

 

[VoG]

Try

Sub atest()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    With Range("A" & i)
        If .Value <> "" And .Offset(, 1).Value = "" Then .Offset(, 1).Interior.ColorIndex = 44
    End With
Next i
End Sub

 

[stevenm]

Hi VoG,

Thanks for quick reply - thought Id post the question then head home from work. Only managed to go get into my riding gear and by the time I got back to my desk you delivered an answer that I just tested and works 100% as I want it to.

Again many thanks.

Steve