changing cell colour based on other and multiple cell values

Hi There,

I am trying to right some vba code that changes the colour of a cell depending on which cell has values filled.

Ie

If cell A2 has a value then cell A1 is red

If cell A2 and A3 has a value then A1 is orange

If Cell A2, A3, A4 has a value then A1 is yellow

If cell A2, A3, A4 and A5 has a value then A1 is Green


The idea above is an example and the cell refs will be different but just an idea of the aim.

I would also have multiple work sheets and want it to run thru each work sheet

Below is some code I started to write but it only makes the a1 cells red and doesn’t then change to orange or yellow when the other cells have the “Y” value

Any help would be greatly appreciated

Private Sub Worksheet_Change(ByVal Target As Range)
   
   
   
   Dim I As Long
   Dim r1 As Range, r2 As Range, r3 As Range, r4 As Range, r5 As Range
   
   For I = 1 To 11
      
   Set r1 = Range("A" & I)
   Set r2 = Range("B" & I)
   Set r3 = Range("C" & I)
   Set r4 = Range("D" & I)
   Set r5 = Range("E" & I)
   
   If r2.Value = "Y" Then r1.Interior.Color = vbRed
   If r2.Value = "Y" & r3.Value = "Y" Then r1.Interior.Color = RGB(255, 192, 0)
   If r2.Value = "Y" & r3.Value = "Y" & r4.Value = "Y" Then r1.Interior.Color = vbYellow
   If r2.Value = "Y" & r3.Value = "Y" & r4.Value = "Y" & r5.Value = "Y" Then r1.Interior.Color = vbGreen
   
   Next I

End Sub

Replace the & with And

cheers that sorted it

You're welcome & thanks for the feedback