VBA code for if cell Background color is yellow then give me "y" if not "n"

[boldcode]

Hi all,

Here is my situation, I have two columns A and B:

Column A Column B
12 y
14 n

If cells in Column A have a background color of yellow, then in the adjacent cells in Column B give a "y" for yes they have a background color of yellow or "n" if they do not have a background color of yellow.

Thanks in advance!

BC

 

[boldcode]

VoG and Ryan,

It works just fine and I appreciate your time except for my column header keeps changing to n

 

[VoG]

Just make this small change

Sub yella()
Dim LR As Long, I As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For I = 2 To LR
    With Range("C" & I)
        .Value = IIf(.Offset(, -2).Value > .Offset(, -1).Value, "y", "n")
    End With
Next I
End Sub

 

[boldcode]

figured it out guys, I just changed For I = 1 To LR to For I = 2 To LR

Sub yella()
Dim LR As Long, I As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For I = 2 To LR
With Range("D" & I)
.Value = IIf(.Offset(, -2).Value > .Offset(, -1).Value, "y", "n")
End With
Next I
End Sub

 

[boldcode]

VoG and Ryan,

Thank you!!! It works just like you guys said, I apologize for not clarifying my question accurately.

Again, Thank you,

BC

 

[VoG]

OK, no problem but next time...

 

[boldcode]

VoG,

Is there a way to run this code without me manually having to run it.

BC

 

[VoG]

What changes the values in columns A and B - you or do they contain formulas?

 

[boldcode]

VoG,

I change the values in A and in B their is a formula. So, the code you gave me works great but I the next thing I am looking for is: when I change the value in cells A I the macro you gave to run automatically without me having to run manually.


Sub yella()
Dim LR As Long, I As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For I = 2 To LR
With Range("C" & I)
.Value = IIf(.Offset(, -2).Value > .Offset(, -1).Value, "y", "n")
End With
Next I
End Sub</pre>

 

[VoG]

Try this: right click the sheet tab and select View Code. Paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
    Application.EnableEvents = False
    With Target.Offset(, 2)
        .Value = IIf(.Offset(, -2).Value > .Offset(, -1).Value, "y", "n")
    End With
    Application.EnableEvents = True
End If
End Sub

then press ALT + Q to return to your sheet.

 

[boldcode]

VoG,

Thank You!!!