change value

[fatalcore]

Hi Friends,

I am stuck at problem, and was hoping if anybody could please help me out with a VBA code that will run with Column G.
I have an excel sheet, in cell G2 I have Team A or Team B or Team C.
Cell position G2 is deciding factor.If G2 is Team A then anything below it should be Team B, or if Team B then anything below it should be Team A.
In this example file G2 value is Team A, but G3 is team B then automatically the corresponding value of C changes to "Retest on next sample".
Then G4 is team B then automatically the corresponding value of C changes to "Retest on next sample".
Then G7 is team C then automatically the corresponding value of C changes to "Retest on next sample".

But if the cell is blank (that means no team) then the code will not change the cell value in Column C.

I am attaching the example file for reference.
Please help me friends,
Thanks in advance,
best regards,
me

[patel45]

Sub a()
LR = Cells(Rows.Count, "A").End(xlUp).Row
team = Range("G2").Text
For j = 2 To LR
  If Range("G" & j).Text = team Then
    Range("C" & j).Value = "Passed"
  Else
    Range("C" & j).Value = "Retest on next sample"
  End If
Next
End Sub

[fatalcore]

Thanks patel45
It works like a charm.
Thanks a ton !
Best regards,
Me.

[Winon]

Nice Coding patel45,

But don't you think though that a simple formula in Cell C3, copied down, could avoid VBA?

Something like:

=IF(G3="","",IF(G3<>$G$2,"Retest on next sample","Passed"))

Why am asking this is just that I believe that it makes it so much easier.

[HaHoBe]

Hi, fatalcore,

did you delete the contents from cell G2 and run patel45īs code? According to your opening post:

But if the cell is blank (that means no team) then the code will not change the cell value in Column C.

Sub shortened_a()
Dim lngCounter As Long
For lngCounter = 2 To Cells(Rows.Count, "A").End(xlUp).Row
  If Not IsEmpty(Range("G" & lngCounter).Value) Then
    Range("C" & lngCounter).Value = IIf(Range("G" & lngCounter).Value = Range("G2").Value, "Passed", "Retest on next sample")
  End If
Next lngCounter
End Sub

Sub shortened_b()
Dim lngCounter As Long
For lngCounter = 2 To Cells(Rows.Count, "A").End(xlUp).Row
  If Not IsEmpty(Range("G2").Value) Then
    Range("C" & lngCounter).Value = IIf(Range("G" & lngCounter).Value = Range("G2").Value, "Passed", "Retest on next sample")
  End If
Next lngCounter
End Sub

@Winon:
I agree on using a formula.

Ciao,
Holger

[patel45]

Nice Coding patel45,
But don't you think though that a simple formula in Cell C3, copied down, could avoid VBA?

yes, I agree, but I love vba and hate formulas

[Winon]

@ HaHoBe,

Thank you HaHoBe.

[Winon]

@ patel45,

yes, I agree, but I love vba...

Glad to have you around then. I stand to learn a lot from you as well!