# Move Triangle Shape based on cell value



## Bui Tao (Dec 23, 2022)

I hope someone can help me!

I want that when I enter a value in cell B3 (or B4), the triangle in cell F3 (or J4) moves along. Likewise, when I enter a value in cell C3 (or C4), the triangle in cell J3 (or Q4) will also move. And how to program when I have many TASK to do. It's too hard for me.
Many thanks.


----------



## Micron (Dec 23, 2022)

Not nearly enough there to provide a definitive solution. F'rinstance,
whether or not the numbers in yellow cells correspond to day number;
where's 1 to 19 on the left
will there ever be 3 to 19 on the right
do numbers repeat because every month has (e.g. day 1)
So here's how to center a shape based on row 4 where you have 24/12/2022

```
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim shp As Shape
Dim i As Integer

If Target.Row = 4 And Target.Column = 10 Then
     i = Day(Cells(4, 10).Value)
     Set rng = ActiveSheet.Range(Cells(4, i - 14), Cells(4, i - 14))
     Set shp = Shapes("5-Point Star 2")
     shp.Left = rng.Left + (rng.Width - shp.Width) / 2
End If

End Sub
```
NOTE - my date was in J4 so that's why I used 4,10 for cell reference. It would be better to use the target address of the edited cell, but maybe this is a start. It all depends on the unknowns that I listed (and probably more). The Day function may not be relevant. I based it on the assumption that there's some sort of relationship between the numbers in yellow cells with the number of the day.
Not sure how much time I could spend on this given the impending Christmas holidays.


----------



## Bui Tao (Dec 23, 2022)

Micron said:


> Not nearly enough there to provide a definitive solution. F'rinstance,
> whether or not the numbers in yellow cells correspond to day number;
> where's 1 to 19 on the left
> will there ever be 3 to 19 on the right
> ...


Oh, thank you so much. It's great. But I want to now that if I have 10 or 50 TASKS to do. There will be many triangles. How can you program?


----------



## Micron (Dec 23, 2022)

Code would have to loop over something - either a set of shapes (and it gets the beginning and ending date values in the rows where the shape is located) or loop over the end and start dates and move the triangles accordingly. I think just about anything is doable, but that doesn't make the task easy. I forgot to mention that when I used Day function result to decide on where to put a shape, it was based on mm/dd/yyyy format, which is not what you are using. What Day returns with your date format would need to be determined IF it in fact, is even relevant. I posted that I wondered about that but so far, still no clarification.

I really think that without a copy of the file, this would take far too many posts to answer all the information that's missing at this point, and there would only be more to come.


----------



## Bui Tao (Dec 23, 2022)

Micron said:


> Code would have to loop over something - either a set of shapes (and it gets the beginning and ending date values in the rows where the shape is located) or loop over the end and start dates and move the triangles accordingly. I think just about anything is doable, but that doesn't make the task easy. I forgot to mention that when I used Day function result to decide on where to put a shape, it was based on mm/dd/yyyy format, which is not what you are using. What Day returns with your date format would need to be determined IF it in fact, is even relevant. I posted that I wondered about that but so far, still no clarification.
> 
> I really think that without a copy of the file, this would take far too many posts to answer all the information that's missing at this point, and there would only be more to come.











						VBA test.xlsm
					






					drive.google.com
				



Okey, you can access this link to get my file. Help me fix the code if I change the start day or finish day with year is 2023.
Thanks a lot!!


----------



## Micron (Dec 24, 2022)

Count of information sought: *4* in post 2
Count of answers I received: *0*

I guess you could just edit the If...End If part to work with your sheet, then copy and edit it 10 times for 10 tasks.


> if I change the start day or finish day with year is 2023.


I have no idea what that means, or how the year makes any difference IF the number of the day is what matters.


----------



## Anthony47 (Dec 29, 2022)

A possible option, via formula, in case the problem is still pending:
-assuming that from F2 and subsequent cells contain the dates
-then set in F4 the formula

```
-format F4 with a font size of 20
-copy F4 to the right for as many dates you have in row 2
-copy this line of formulas downward for the expected list of activity

The result will be similar to the attached image, rows 4-6

You might use Wingdings 3 formatting, and obtain the result shown in row 8; in this case the formula in F8 is
[code]=IF(F$2=$B8,"s"&REPT(CHAR(173),3),IF(F$2=$C8,REPT(CHAR(173),3)&"s",IF(AND(F$2>$B8,F$2<$C8),REPT(CHAR(173),8),IF(AND(F$2<=TODAY(),F$2>$B8,$B8<>"",$C8=""),REPT(CHAR(173),7),""))))
```

(see the image for the real look of line 8)

byBUI TAO-MrEx_C21225_VBA test.xlsmABCDEFGHIJKLMNOPQRSTUVW1TaskStart DayFinish Day2022-dic          2022-dic2023-gen     22021222324252627282930310102030405063Design Mold20/12/202231/12/20224Manufacture21/12/202230/12/2022 V____________________________________________V       5Assemble124/12/2022    V______________________        6Assemble222/12/202225/12/2022  V______________V            7Delivery                  8Task 126/12/202228/12/2022      s s         9Task 2                  10Task 311Task 412Task 5Sheet1Cell FormulasRangeFormulaF1:W1F1=IF(MONTH(F2)<>MONTH(G2),TEXT(F2,"aaaa-mmm"),IF(MONTH(E2)<>MONTH(F2),TEXT(F2,"aaaa-mmm"),""))G2:W2G2=F2+1F4:W7F4=IF(F$2=$B4,"V__",IF(F$2=$C4,"__V",IF(AND(F$2>$B4,F$2<$C4),"_____",IF(AND(F$2<=TODAY(),F$2>$B4,$B4<>"",$C4=""),"____",""))))F8:W8F8=IF(F$2=$B8,"s"&REPT(CHAR(173),3),IF(F$2=$C8,REPT(CHAR(173),3)&"s",IF(AND(F$2>$B8,F$2<$C8),REPT(CHAR(173),8),IF(AND(F$2<=TODAY(),F$2>$B8,$B8<>"",$C8=""),REPT(CHAR(173),7),""))))F9:W9F9=IF(F$2=$B9,"s4",IF(F$2=$C9,"4s",IF(AND(F$2>$B9,F$2<$C9),4,IF(AND(F$2<=TODAY(),F$2>$B9,$B9<>""),4,""))))


----------

