Previous Topic Next Topic

Grey out an cell and not allowed to enter depending on a cell

Solved/Closed
Raj_1562 Posts 31 Registration date Wednesday August 26, 2020 Status Member Last seen August 29, 2022 - Updated on Sep 29, 2020 at 08:13 AM
Raj_1562 Posts 31 Registration date Wednesday August 26, 2020 Status Member Last seen August 29, 2022 - Oct 1, 2020 at 12:02 PM
Hi All,

Can you please help me on below request?

In column B the name is entered the Column D partner name cell will be grey out and don't allow to user to select the value from the drop-down (Column D is a dropdown).

In Column D the name is selected from the drop-down the column B cell will be grey out or show warning/ error message or the Column B value disappear after selecting the value in the column D.



Thanks,
Raj

6 replies

Answer 1 / 6
TrowaD Posts 2901 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 525
Sep 29, 2020 at 12:01 PM
Hi Raj,

There is a flaw in your logic.

In your first statement you don't want users to enter data in column D if column B has a value. But then in your second statement, you want users to be able to enter a value in D to clear the value in column B, which isn't possible according to your first statement.

I did wanted to give you something:
The code below looks at column B, when you enter a value, the corresponding cell in column D cleared and made grey.
The code also looks at column D, when you enter a value, the corresponding cell in column B cleared and made grey.

Here is the code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Long

lRow = Range("A2").End(xlDown).Row

If Not Intersect(Range("B3:B" & lRow), Target) Is Nothing Then
    If Target.Value <> vbNullString Then
        Range("D" & Target.Row).Value = vbNullString
        Range("D" & Target.Row).Interior.ColorIndex = 15
    Else
        Range("D" & Target.Row).Interior.ColorIndex = xlNone
    End If
ElseIf Not Intersect(Range("D3:D" & lRow), Target) Is Nothing Then
    If Target.Value <> vbNullString Then
        Range("B" & Target.Row).Value = vbNullString
        Range("B" & Target.Row).Interior.ColorIndex = 15
    Else
        Range("B" & Target.Row).Interior.ColorIndex = xlNone
    End If
End If
End Sub


Best regards,
Trowa
1
Answer 2 / 6
TrowaD Posts 2901 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 525
Oct 1, 2020 at 11:24 AM
Hi Raj,

Target refers to a single cell. When you clear the commented range, more then 1 cell changes and Excel gets confused.

To prevent the error, you will want to count the cells that get changed and when it is more then 1 then exit the sub.

Place the code line below on code line 13: 
If Target.Cells.Count > 1 Then Exit Sub


Best regards,
Trowa
1
Answer 3 / 6
Raj_1562 Posts 31 Registration date Wednesday August 26, 2020 Status Member Last seen August 29, 2022
Sep 29, 2020 at 12:30 PM
Hi Trowa,

Great!

It's work good.

Thanks,
Raj
0
Answer 4 / 6
Raj_1562 Posts 31 Registration date Wednesday August 26, 2020 Status Member Last seen August 29, 2022
Updated on Sep 29, 2020 at 01:07 PM
Hi Trowa,

I'm getting the below error while implementing the same logic in my original source file.


after commenting the below code the error has been resolved. but the colour does not appear in my file (I THINK I HAVE A TABLE, So THE COLOR LOGIC IS NOT WORKING IN MY FILE).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Long

lRow = Range("C9").End(xlDown).Row

If Not Intersect(Range("J9:J" & lRow), Target) Is Nothing Then
If Target.Value <> vbNullString Then
Range("K" & Target.Row).Value = vbNullString
' Range("K" & Target.Row).Interior.ColorIndex = 15
'Else
' Range("K" & Target.Row).Interior.ColorIndex = xlNone
End If
ElseIf Not Intersect(Range("K9:K" & lRow), Target) Is Nothing Then
If Target.Value <> vbNullString Then
Range("J" & Target.Row).Value = vbNullString
' Range("J" & Target.Row).Interior.ColorIndex = 15
'Else
'Range("J" & Target.Row).Interior.ColorIndex = xlNone
End If
End If
End Sub

And How can I include the below clear content code in the above logic.

If Intersect(Target, Range("K3")) Is Nothing Then Exit Sub

Range("D9:G210").ClearContents
Range("J9:K210").ClearContents
Range("M9:AR210").ClearContents

Thanks,
Raj
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 6
Raj_1562 Posts 31 Registration date Wednesday August 26, 2020 Status Member Last seen August 29, 2022
Updated on Sep 30, 2020 at 08:22 AM
Hi Trowa,

The below code is working fine with my file. but I couldn't able to clear the J9: K210 data.

If I uncomment the commented code getting error. 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Long

lRow = Range("I9").End(xlDown).Row

    If Not Intersect(Target, Range("k3")) Is Nothing Then
        Range("D9:G210").ClearContents
        'Range("J9:K210").ClearContents   ' comment code
        Range("M9:AR210").ClearContents
    End If
    
    If Not Intersect(Range("J9:J" & lRow), Target) Is Nothing Then
    
    If Target.Value <> vbNullString Then
        Range("K" & Target.Row).Value = vbNullString
    End If
    
    ElseIf Not Intersect(Range("K9:K" & lRow), Target) Is Nothing Then
    
    If Target.Value <> vbNullString Then
        Range("J" & Target.Row).Value = vbNullString
    End If
    
End If
End Sub


Thanks,
Raj
0
Answer 6 / 6
Raj_1562 Posts 31 Registration date Wednesday August 26, 2020 Status Member Last seen August 29, 2022
Oct 1, 2020 at 12:02 PM
Hi Trowa,

Awesome!

Works as expected!!!

Regards,
Raj
0