VBA to check same conditions across columns and put result in a new column

[Alroj]

Hi there,

I have written this VBA that checks column E,F and G and if either of the cells have been coloured with yellow then I need to show the number 1 in column N. If no color if found in any of these columns then the number in column N should be zero.
The macro below is giving me the results across columns J thorugh to column P. Would you please help me to adjust this macro so the results are shown only in column N? Much appreciated

Sub SelectCellsHighlightedInYellow()

Dim c As Range

For Each c In Range("A8:G600")
If c.Interior.ColorIndex = 36 Or c.Offset(0, 1).Interior.ColorIndex = 36 Or c.Offset(0, 2).Interior.ColorIndex = 36 Then
c.Offset(0, 9).Value = 1
Else
c.Offset(0, 9).Value = 0
End If
Next

End Sub

 

[Micron]

Maybe

     If c.Interior.ColorIndex = 36 Or c.Offset(0, 1).Interior.ColorIndex = 36 Or c.Offset(0, 2).Interior.ColorIndex = 36 Then
          Cells(c.Row, 14) = 1
     Else
          Cells(c.Row, 14) = 0
     End If

However, given that you're looping over every cell in a range I'm not sure you need 3 logical checks. This may or may not be what you really need:

     If c.Interior.ColorIndex = 36 Then
          Cells(c.Row, 14) = 1
     Else
          Cells(c.Row, 14) = 0
     End If

 

[Alroj]

Maybe

     If c.Interior.ColorIndex = 36 Or c.Offset(0, 1).Interior.ColorIndex = 36 Or c.Offset(0, 2).Interior.ColorIndex = 36 Then
          Cells(c.Row, 14) = 1
     Else
          Cells(c.Row, 14) = 0
     End If

However, given that you're looping over every cell in a range I'm not sure you need 3 logical checks. This may or may not be what you really need:

     If c.Interior.ColorIndex = 36 Then
          Cells(c.Row, 14) = 1
     Else
          Cells(c.Row, 14) = 0
     End If

Micron,

Both vba's only worked for the color cell in column G and didn't ignore colour cells in column E and F.
The situation I have is that Column E has 10 cells that are in yellow, then column F also has cells also in yellow but not the same cells as column E or G. So, I am trying to create a summary of the 3 columns with at least one yellow cell or more.

Thank you for your help

 

[Alroj]

Sorry I meant to say

"Both vba's only worked for the color cell in column G and ignored colour cells in column E and F"

 

[Peter_SSs]

Give this a try

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Sub Check_Yellow()
  Dim c As Range
  
  For Each c In Range("E8:E600")
    Range("N" & c.Row).Value = -(c.Interior.ColorIndex = 36 Or c.Offset(0, 1).Interior.ColorIndex = 36 Or c.Offset(0, 2).Interior.ColorIndex = 36)
  Next c
End Sub

 

[Alroj]

Peter_SSs, thank you for assistance. Your VBA worked very well.
Much appreciated

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.