Greetings,
I am using the following code to change the color in an autoshape depending on the cell value. (this is just a snippet as I am using this code to control 200+ autoshapes) and this code works very well.
I have added the following code to a command button to empty out the values in the cells that these autoshapes are linked to:
And this works fine, I would like all of the autoshapes colors to turn back to clear or "no fill" once these values return to 0 or blank as it were. I have tried tweaking the code so that the red color is ranged from 1-421 instead of <=421 or by adding in another Else statement relecting the null value, but it isn't working - I am sure that I am just not writing the syntax properly (rookie!). Could anyone make a suggestion? Thanks
I am using the following code to change the color in an autoshape depending on the cell value. (this is just a snippet as I am using this code to control 200+ autoshapes) and this code works very well.
HTML:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$46" Then
'Change autoshape color to red depending upon cell value
With ActiveSheet.Shapes("Rectangle 1").Fill.ForeColor
If Target.Value <= 421 Then
.SchemeColor = 10
Else: .SchemeColor = 50
End If
End With
ElseIf Target.Address = "$C$47" Then
With ActiveSheet.Shapes("Rectangle 2").Fill.ForeColor
If Target.Value <= 421 Then
.SchemeColor = 10
Else
.SchemeColor = 50
End If
End With
I have added the following code to a command button to empty out the values in the cells that these autoshapes are linked to:
HTML:
Sub RemoveValue()
Range("C46:C66").Select
Selection.ClearContents
End Sub
And this works fine, I would like all of the autoshapes colors to turn back to clear or "no fill" once these values return to 0 or blank as it were. I have tried tweaking the code so that the red color is ranged from 1-421 instead of <=421 or by adding in another Else statement relecting the null value, but it isn't working - I am sure that I am just not writing the syntax properly (rookie!). Could anyone make a suggestion? Thanks