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

 

[Spiffierlime]

I'm confused, in VBA there is with all the data see PNG

 

[Fluff]

That sheet is called MSFWBS so either use

If Range("V" & i).Value = Sheets("MSFWBS").Range("D3").Value = ("Complete") Then

or

If Range("V" & i).Value = Sheet8.Range("D3").Value = ("Complete") Then

But that line of code makes little sense. What is the value of V3?

 

[Spiffierlime]

Ok I Striped this down, Created a new sheet with 2 shapes named Oval 1 and Oval 2

The command button cycles through the code and debugs out after "else" "saying can't find, although when I hover over Oval 1 a hand appears.

The bold section in code below is the debug in yellow

Private Sub CommandButton1_Click()
 Dim i As Long

For i = 1 To 2
If Range("V" & i).Value = Sheets("MSFWBS").Range("D3").Value = ("Complete") Then

ActiveSheet.Shapes("Oval " & i + 1).Fill.ForeColor.RGB = RGB(252, 25, 255)
Else
[U][B]ActiveSheet.Shapes("Oval " & i + 1).Fill.ForeColor.RGB = RGB(252, 255, 255)[/B][/U]

End If
Next i

End Sub

 

[Fluff]

What are the values in col V?

 

[Spiffierlime]

I don't know, you Put V in the original code??? The Data range is in sheet MSFWBS column D, the Ovals are on the Activesheet, each Oval has a seperate name, I have not grouped the ovals as I need them to only fill when the corrisponding cell details "Complete" in column D Sheet MSFWBS, Perplexted

 

[Fluff]

I don't know, you Put V in the original code???

Take a look at your original post!!!
Lets start from scratch & this time you need to say exactly what you are trying to do, without simplifying anything.

 

[Spiffierlime]

So Sorry your right my Bad,

Start.

Sheet MSFWSB Coumn A is a stage in a project, Column D in the same sheet allows the user to enter the word "Complete" after each stage is completed. I have a snake chart on Sheet 2 with Ovals, Each Oval refers to a stage completion on sheet MSFWSB, Names of the Ovals are Oval 1, Oval 2 and so on. when the user hits the command button on the sheet 1 with the Snake Chart on, I need the corrisonding Oval to be filled with Pink else remains Yellow. based on wether the stage is complete on WSPWSB or not.

 

[Fluff]

Ok try

Sub Spifferlime()
   Dim i As Long
   
   For i = 1 To 2
      If Sheets("MSFWSB").Range("D" & i).Value = "Complete" Then
         ActiveSheet.Shapes("Oval " & i).Fill.ForeColor.RGB = RGB(252, 74, 255)
      Else
         ActiveSheet.Shapes("Oval " & i).Fill.ForeColor.RGB = RGB(252, 255, 0)
      End If
   Next i
End Sub

 

[Spiffierlime]

I get subscribt out of Range

If Sheets("MSFWSB").Range("D" & i).Value = "Complete" Then

 

[Fluff]

That means you don't have a sheet called MSFWSB.
Try

If Sheet8.Range("D" & i).Value = "Complete" Then