Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

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

Report
Posts
22
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
January 18, 2021
 -
Posts
22
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
January 18, 2021
 -
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
Posts
2718
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 15, 2021
 459
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
Comment
1
Thank you
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 2942 users have said thank you to us this month

Answer 2 / 6
Posts
2718
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 15, 2021
 459
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
Comment
1
Thank you
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 2942 users have said thank you to us this month

Answer 3 / 6
Posts
22
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
January 18, 2021
Hi Trowa,

Great!

It's work good.

Thanks,
Raj
Answer 4 / 6
Posts
22
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
January 18, 2021
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
Answer 5 / 6
Posts
22
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
January 18, 2021
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
Answer 6 / 6
Posts
22
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
January 18, 2021
Hi Trowa,

Awesome!

Works as expected!!!

Regards,
Raj
Reply Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!