Color shape won`t change automate only after double click and enter

[otrava18]

Hello all,

I have an issue with a shape. The shape color depends on a row where some columns are hide or not hide. Thanks to @DanteAmor i can count the hide or not hide cells from that row, but the shape doesn`t want to cooperate. If i hide some columns from that row the shape doesn`t change it`s color (the behinde formula and result is it ok) only if i double click on any cell and hit the enter. I don`t know how to do this "refresh" in VBA. Can you give me an advise ?

Private Sub Worksheet_Change(ByVal Target As Range)
'The Range ("V2") it`s count the number of hide cells and gave me a "READY" or "NOT READY".
If Worksheets("VIDEO 2 FIRE").Range("V2").Value = "READY" Then
ThisWorkbook.Sheets("STRUCT 2 FIRE").Shapes("Square1").Fill.ForeColor.RGB = RGB(154, 192, 74)
ThisWorkbook.Sheets("STRUCT 2 FIRE").Shapes("Square1").Fill.BackColor.RGB = RGB(154, 192, 74)
Else
ThisWorkbook.Sheets("STRUCT 2 FIRE").Shapes("Square1").Fill.ForeColor.RGB = RGB(204, 64, 61)
ThisWorkbook.Sheets("STRUCT 2 FIRE").Shapes("Square1").Fill.BackColor.RGB = RGB(204, 64, 61)
End If
End Sub

Thank you for your help !

 

[GWteB]

The Worksheet_Change event is not triggered by hiding rows or columns. You could use the Worksheet_Calculate event provided you have a formula on your sheet that evaluates to a different value each time you hide or unhide a column or row.

 

[otrava18]

The Worksheet_Change event is not triggered by hiding rows or columns. You could use the Worksheet_Calculate event provided you have a formula on your sheet that evaluates to a different value each time you hide or unhide a column or row.

Everithing is working good only with this exception. The complete code is this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Worksheets("VIDEO 2 FIRE").Range("V2").Value = "READY" Then
ThisWorkbook.Sheets("STRUCT 2 FIRE").Shapes("Square1").Fill.ForeColor.RGB = RGB(154, 192, 74)
ThisWorkbook.Sheets("STRUCT 2 FIRE").Shapes("Square1").Fill.BackColor.RGB = RGB(154, 192, 74)
Else
ThisWorkbook.Sheets("STRUCT 2 FIRE").Shapes("Square1").Fill.ForeColor.RGB = RGB(204, 64, 61)
ThisWorkbook.Sheets("STRUCT 2 FIRE").Shapes("Square1").Fill.BackColor.RGB = RGB(204, 64, 61)
End If
    If Target.Address(0, 0) = "B3" Then
        If Target.Value > 0 And Target.Value < 11 Then
            Columns("D:L").Hidden = False
            If Target.Value < 10 Then Range(Columns("C").Offset(, Target.Value), "L:L").EntireColumn.Hidden = True
        End If
    ElseIf Target.Address(0, 0) = "B6" Then
        If Target.Value > 0 And Target.Value <= 15 Then
            Rows("10:23").Hidden = True
            If Target.Value > 0 Then Rows(9).Resize(Target.Value).Hidden = False
                   End If
        End If
        Application.ScreenUpdating = True
        Application.CalculateFullRebuild
End Sub

Only this color change doesn`t work. I need to do a refresh. I have tried to insert F2 key in the code but it doesn`t work well.

 

[mikerickson]

Is there a space in "Square 1"?

 

[otrava18]

Is there a space in "Square 1"?

Hello Mike,

No. Seems to be but is not a space there.

Thanks !

 

[GWteB]

If cell V2 evaluates to "ready" depending on the value of cells read in this event procedure in order to decide to hide rows / columns or not, you should propably have to change the sequence of your code, for example:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address(0, 0) = "B3" Then
        If Target.Value > 0 And Target.Value < 11 Then
            Columns("D:L").Hidden = False
            If Target.Value < 10 Then Range(Columns("C").Offset(, Target.Value), "L:L").EntireColumn.Hidden = True
        End If
    ElseIf Target.Address(0, 0) = "B6" Then
        If Target.Value > 0 And Target.Value <= 15 Then
            Rows("10:23").Hidden = True
            If Target.Value > 0 Then Rows(9).Resize(Target.Value).Hidden = False
        End If
    End If

    If Worksheets("VIDEO 2 FIRE").Range("V2").Value = "READY" Then
        ThisWorkbook.Sheets("STRUCT 2 FIRE").Shapes("Square1").Fill.ForeColor.RGB = RGB(154, 192, 74)
        ThisWorkbook.Sheets("STRUCT 2 FIRE").Shapes("Square1").Fill.BackColor.RGB = RGB(154, 192, 74)
    Else
        ThisWorkbook.Sheets("STRUCT 2 FIRE").Shapes("Square1").Fill.ForeColor.RGB = RGB(204, 64, 61)
        ThisWorkbook.Sheets("STRUCT 2 FIRE").Shapes("Square1").Fill.BackColor.RGB = RGB(204, 64, 61)
    End If

    Application.ScreenUpdating = True
    Application.CalculateFullRebuild
End Sub

 

[otrava18]

If cell V2 evaluates to "ready" depending on the value of cells read in this event procedure in order to decide to hide rows / columns or not, you should propably have to change the sequence of your code, for example:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address(0, 0) = "B3" Then
        If Target.Value > 0 And Target.Value < 11 Then
            Columns("D:L").Hidden = False
            If Target.Value < 10 Then Range(Columns("C").Offset(, Target.Value), "L:L").EntireColumn.Hidden = True
        End If
    ElseIf Target.Address(0, 0) = "B6" Then
        If Target.Value > 0 And Target.Value <= 15 Then
            Rows("10:23").Hidden = True
            If Target.Value > 0 Then Rows(9).Resize(Target.Value).Hidden = False
        End If
    End If

    If Worksheets("VIDEO 2 FIRE").Range("V2").Value = "READY" Then
        ThisWorkbook.Sheets("STRUCT 2 FIRE").Shapes("Square1").Fill.ForeColor.RGB = RGB(154, 192, 74)
        ThisWorkbook.Sheets("STRUCT 2 FIRE").Shapes("Square1").Fill.BackColor.RGB = RGB(154, 192, 74)
    Else
        ThisWorkbook.Sheets("STRUCT 2 FIRE").Shapes("Square1").Fill.ForeColor.RGB = RGB(204, 64, 61)
        ThisWorkbook.Sheets("STRUCT 2 FIRE").Shapes("Square1").Fill.BackColor.RGB = RGB(204, 64, 61)
    End If

    Application.ScreenUpdating = True
    Application.CalculateFullRebuild
End Sub

I did something and now i see that is working well. I put the below part in the code.

        Range("B8").Select
        Application.SendKeys ("{F2}")
        Application.SendKeys ("~")

I put it in the both parts. Select a cell "press" F2 and after "press" enter.


Thank you for your time !

 

[GWteB]

You're welcome. Glad you got it working.