Need some help I am a newbie to VBA

[Spiffierlime]

Need help solving my VBA Riddle please someone with the professional knowledge, Please

VBA

Private Sub CommandButton3_Click()

If Range("V1").Value = ("Complete") Then ' this code needs changing to look at a cell on another sheet i.e sheet 1 Range A1
ActiveSheet.Shapes("Oval 57").Fill.ForeColor.RGB = RGB(252, 74, 235)
Else
ActiveSheet.Shapes("Oval 57").Fill.ForeColor.RGB = RGB(255, 255, 255)

End If
'Need to loop this code to check Range V2 and fill Oval 58 and so on RangeV3, and fill oval 59, RangeV4 and fill Oval 60 untill the end
End Sub

 

[Fluff]

Where is the last cell to check in col V or can it vary?

 

[Spiffierlime]

Where is the last cell to check in col V or can it vary?

V3:V69 but the data is on another sheet 1 D3:D69

 

[Fluff]

So you don't want to look at A1 as you said in your post?

 

[Spiffierlime]

Well A1 can be used may change the destination cell later

 

[Fluff]

Ok, try

Sub Spifferlime()
   Dim i As Long
   
   For i = 3 To 69
      If Range("V" & i).Value = Sheets("Sheet1").Range("A1").Value Then
         ActiveSheet.Shapes("Oval " & i + 54).Fill.ForeColor.RGB = RGB(252, 74, 255)
      Else
         ActiveSheet.Shapes("Oval " & i + 54).Fill.ForeColor.RGB = RGB(252, 255, 255)
      End If
   Next i
End Sub

 

[Spiffierlime]

Ok, try

Sub Spifferlime()
   Dim i As Long
  
   For i = 3 To 69
      If Range("V" & i).Value = Sheets("Sheet1").Range("A1").Value Then
         ActiveSheet.Shapes("Oval " & i + 54).Fill.ForeColor.RGB = RGB(252, 74, 255)
      Else
         ActiveSheet.Shapes("Oval " & i + 54).Fill.ForeColor.RGB = RGB(252, 255, 255)
      End If
   Next i
End Sub

not working for me, don't think i am explaining issue in enougth detail please be paitent with me.

 

[Spiffierlime]

As the user hits complete on each row there is a corrisponding shape on sheet 8 that needs to filled in, sorry

 

[Spiffierlime]

Private Sub CommandButton3_Click()

Dim i As Long

For i = 3 To 69
If Range("V" & i).Value = Sheets("Sheet8").Range("D3").Value = ("Complete") Then
ActiveSheet.Shapes("Oval " & i + 54).Fill.ForeColor.RGB = RGB(252, 74, 255)
Else
ActiveSheet.Shapes("Oval " & i + 54).Fill.ForeColor.RGB = RGB(252, 255, 255)
End If
Next i
End Sub

 

[Fluff]

That error means you don't have a sheet called "Sheet8"