jeudi 2 février 2017

VBA *For Each Cell Loop* IF (Value in Column 1) = x AND IF (Equivalent Value In Column 2) >7 THEN

I am having an issue with VBA that I can't seem to find an answer to online. I only started teaching myself a couple of weeks ago so apologies if this is a fairly simple answer...

I am trying to write a macro where the entire row is un-coloured (Is that a word??) based on a value in column E and the equivalent value in column AN (same row). What I have so far is:

For Each cell In Sheets(5).Range("E9:E" & LastRow)

If (cell.Value = "BA" Or cell.Value = "NH" Or cell.Value = "AD") Then
    If ActiveCell.Offset(0, 35) > 7 Then
          cell.EntireRow.Interior.color = xlNone
End If
Next cell

The only problem is, when I try to run it gives the 'Compile error: Next without For' error message.

?? Am I missing something... there is a 'For'...

If I remove the below line then the code runs,

If ActiveCell.Offset(0, 35) > 7 Then

but it's not the output i require as all of the BA, NH & AD values in column A have their entire row un-coloured regardless of whether they are >7 or <7.

Is the error caused by the IF-THEN-IF statement?

Aucun commentaire:

Enregistrer un commentaire