VBA Conditional

[Chassmin]

Hello everybody

I am here because I am trying to write a very easy code.
The idea is that if in Column A it says TBR and at the same time in Column E it says No, the E cell where it says No turns into Yellow.

I wrote this code but it does not work. Please please help me

Thank you so much

Sub condition()
    If Range("A5, A1700") = "TBR" And Range("E5, E1700") = "No" Then
    
    Range("E:E").Selection.Interior.Color = 3
    
    End If
    
End Sub

 

[richarddawson]

Sub condition()

dim J as long
for j = 5 to 1700
if cells(j, 1) = "TBR" And cells(j, 5) = "No" Then

range("a" & j & ":e" & j).Selection.Interior.Color = 3

End If
next j
End Sub

 

[Chassmin]

Thank you but it does not work :l

 

[richarddawson]

Sorry,

I was editing your original and did not realise [TAB] caused it to post.
The version I have posted colours 5 cells.

 

[Scott T]

VBA is case sensitive. In excel No=no but in VBA No<> no. You will need to either make sure your sheet has the correct case in column A and E or modify the code to make the value compared the same case as your strings like below.

If UCase(Cells(x, 1)) = "TBR" And UCase(Cells(x, "E")) = "NO" Then

 

[Chassmin]

it says that range of the object _global failed

 

[My Aswer Is This]

Try this:

This script looks down column A from row 1 to the last row in column "A" with data.

Sub Test()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To Lastrow
        If Cells(i, 1).Value = "TBR" And Cells(i, "E").Value = "No" Then Cells(i, "E").Interior.ColorIndex = 6
    Next
Application.ScreenUpdating = True
End Sub

 

[Chassmin]

Hello, all the No have N and therefore there is only one string No and only one string for TBR all in cap letters as well.

What I am trying to do is that in the same row we have TBR and No, then the cell in column E of that row is coloured.

Technically I have to have No and TBR in the same row, I think that is what is missing?

Thank you

 

[Chassmin]

Omg it works amazing thank you so much

 

[My Aswer Is This]

What worked?

Omg it works amazing thank you so much