Hide a data

[RJ1969]

Hello,

I am not sure if I needed a vba code or conditional formatting on this scenario.

I want to hide the "200" in D4:D3000 until an entry is being entered in B4:B3000. If there is no entry in B4:B3000 then the D4:D3000 should be blank.

Thank you in advance.

[ORoos]

Hi there,

In cell 'D4" try:

=IF(B4<>"",200,"")

and pull down.

[RJ1969]

Hi
Oroos!

Can this be done inVBA code?

[ORoos]

Hi again,

Yes that can be done with VBA.
The code is in the worksheet and is triggered when a change in column B (rows 4-3000 [can be updated..]) is changed.
If there is anything in column B, column D will show 200. If column B value is deleted, then the 200 in column D is also cleared.

Trust this helps.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim WorkRng As Range, roww As Long
    Dim rng As Range
    
    Set WorkRng = Intersect(Range("B4:B3000"), Target)
    If Not WorkRng Is Nothing Then
        Application.EnableEvents = False
            For Each rng In WorkRng
                roww = rng.Row
                If Not rng.Value = "" Then
                    Cells(roww, "D").Value = 200  ' <- this is the value to show in column D
                Else
                    Cells(roww, "D").ClearContents ' <- Clear column D if column B is cleared
                End If
            Next
        Application.EnableEvents = True
    End If
End Sub

[Sintek]

Another Option...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B4:B3000")) Is Nothing Then
    Application.EnableEvents = False
    If Target.CountLarge > 1 Then
        Target.Offset(, 2).Resize(Target.Cells.Count) = ""
    ElseIf Target <> "" Then
        Target.Offset(, 2) = 200
    Else
        Target.Offset(, 2) = ""
    End If
    Application.EnableEvents = True
End If
End Sub

[RJ1969]

Thank you both!!!!

[Sintek]

Glad to have helped...Tx for rep +

[RJ1969]

Hello guys,

I would like to add "no" in column H. No should be hide if no entry in B along with 200 which is in Column D.

Thank you again.

[ORoos]

Hi there,

I realize your request is some weeks back (was away) below is an updated code which should do what you require.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim WorkRng As Range, roww As Long
    Dim rng As Range
    
    Set WorkRng = Intersect(Range("B4:B3000"), Target)
    If Not WorkRng Is Nothing Then
        Application.EnableEvents = False
            For Each rng In WorkRng
                roww = rng.Row
                If Not rng.Value = "" Then
                    Cells(roww, "D").Value = 200  ' <- this is the value to show in column D
                    Cells(roww, "H").Value = "No"  ' <- this is the value to show in column H
                Else
                    Cells(roww, "D").ClearContents ' <- Clear column D if column B is cleared
                    Cells(roww, "H").ClearContents ' <- Clear column H if column B is cleared
                End If
            Next
        Application.EnableEvents = True
    End If
End Sub