Hide/Unhide the same shape on any active cell with a specified value

[Piaget]

I am working with the code to hide or unhide a shape whenever a value 1 is entered on a cell. The range includes from J13:AC166. Using the code below it will take more lines to have all the range included. I know there is a way to optimize it. All shapes are of the same look with different names rt1, rt2, rt3... etc. I am really a new coder here. Any help will be much appreciated.


Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveSheet.Range("E13").Value = 1 Then
ActiveSheet.Shapes("rt1").Visible = True
Else
ActiveSheet.Shapes("rt1").Visible = False
End If

If ActiveSheet.Range("F13").Value = 1 Then
ActiveSheet.Shapes("rt2").Visible = True
Else
ActiveSheet.Shapes("rt2").Visible = False
End If

If ActiveSheet.Range("G13").Value = 1 Then
ActiveSheet.Shapes("rt3").Visible = True
Else
ActiveSheet.Shapes("rt3").Visible = False
End If

If ActiveSheet.Range("H13").Value = 1 Then
ActiveSheet.Shapes("rt4").Visible = True
Else
ActiveSheet.Shapes("rt4").Visible = False
End If

If ActiveSheet.Range("I13").Value = 1 Then
ActiveSheet.Shapes("rt5").Visible = True
Else
ActiveSheet.Shapes("rt5").Visible = False
End If

If ActiveSheet.Range("J13").Value = 1 Then
ActiveSheet.Shapes("rt6").Visible = True
Else
ActiveSheet.Shapes("rt6").Visible = False
End If

...

End Sub

 

[Piaget]

o are you adding the shapes to the cells manually or do you have a script to do this.
If you have a script please show it to me. - yes i did manually tried adding 20 shapes for testing because i have no idea to code it. but if there is a script you can offer, i will love to try it.

And are you putting the shapes in the range J13:AC166 - yes

And does the shape fill the cell completely? - nope. just half right shaded triangle. the same with the example i posted.

I have a script that would enter a shape into this range with no copy involved. - i would love to have it.

But I would need to know exactly what type shape and where to enter it. - each cell is assigned a shape to show and hid. when a value 1 is entered the shape on it is show whilst other value, it is hidden.

Thank you again for the time. I welcome any comment.

 

[Fluff]

Whilst I appreciate that you said it must be shapes.
You can do something very similar just using Conditional formatting.
A sample of which can be found here https://1drv.ms/x/s!AtYrlcV2NWPFgR7cujSetJwvKEum

 

[My Aswer Is This]

With your wanting to only fill the right half of the cell and your wanting the shape to be shaded some color.

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

It would be of interest to me.

If this is a project to show attendance for 3900 students or so I was wondering how this would work.
Just seeing a triangle next to their name or such would give some visual indicator but no factual indication
How many students have a triangle next to there name or such


Have you ever used a Excel Table?

Tables are great for displaying data visually and can also supplies statistical information

Like how many times was Janie absent this week this year on and on
And can display all the rows assuming each student has a separate record know as a Row in Excel

And using a Slicer as part of your table is very easy way to get the results you want from your Table.

Just a thought.
And a Table using Slicer requires no Vba code.

 

[Piaget]

So are you adding the shapes to the cells manually or do you have a script to do this.
If you have a script please show it to me. - yes. i did add a shape for each cell manually coz I dont have a scriot to do that.

And are you putting the shapes in the range - yes.J13:AC166

And does the shape fill the cell completely? - yes.

I have a script that would enter a shape into this range with no copy involved. - can i have it? thank you.

That's why in previous post I mentioned this.

But I would need to know exactly what type shape and where to enter it.

 

[JLGWhiz]

The problem I am having is that there is not method that I know of to tie a specific shape out of hundreds of shapes to a specific cell, since they are on separate graphic platforms within the application. Writing an algorithm to do the task would be prohibitive for 3912 shapes and cells. You should seriously consider alternative methods to accomplish your objective as per a couple of suggestions above. Conditional formatting is a built in facility that can easily be applied by the user.