Hide cell value if another cells value =

[ipbr21054]

Hi,
I would like to hide a cell value if a specific cell value contains a specific word.
Cell M11 is a drop down with two options CAre or BIKE
If CAR is selected then i wish to hide the values in cells O14 & O17

Trying with the code below i am being today doesnt support support this property or method with the line shown in Red

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Range("M11").Value = "CAR" Then
  Range ("O14").Visible = False
    End If
End Sub

 

[DanteAmor]

It is not possible to hide the cell value, since the cell does not have that property.

What you can do is change the cell color to white (for example) and the font color to white as well. That will create an "invisible" effect.

Before:

After:

For the above, replace your code with the following:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$M$11" Then
    If Target.Count > 1 Then Exit Sub
 
    With Range("O14,O17")
      If Target.Value = "CAR" Then
        .Interior.Color = vbWhite
        .Font.Color = vbWhite
      Else
        .Interior.Color = xlNone
        .Font.Color = vbBlack
      End If
    End With
  End If
End Sub

-------------------------
There is a detail, if you select cell O14, the value is "hidden" in the cell, but in the formula bar you will see the value:

If you also want to hide the value in the formula bar. Then do the following:

Select the sheet where you want this to work, and run this macro only once:

Sub prepare_sheet()
  ActiveSheet.Unprotect
  Cells.Locked = False
  Cells.FormulaHidden = False
  With Range("O14,O17")
    .FormulaHidden = True
  End With
  ActiveSheet.Protect
End Sub

What the macro does is:

- Unlock all the cells.​

- Change the Hidden option only to cells O14 and O17.​

- At the end, the macro protects the sheet, so that the above takes effect.​

Now if you select cell O14 the value is hidden in the cell and in the formula bar:

Regards
Dante Amor

 

[ipbr21054]

Thanks.
Will look at that tomorrow.
So just a question for now.

My worksheet is coloured grey.
I can find the #????? Reference but can I use it in place of vbWhite?

.Interior.Color = vbWhite

 

[DanteAmor]

I can find the #????? Reference but can I use it in place of vbWhite?

For example:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$M$11" Then
    If Target.Count > 1 Then Exit Sub
    
    With Range("O14,O17")
      If Target.Value = "CAR" Then
        .Interior.Color = 12566463
        .Font.Color = 12566463
      Else
        .Interior.Color = 12566463
        .Font.Color = vbBlack
      End If
    End With
  End If
End Sub

Ex:

 

[ipbr21054]

Thanks again works well