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

Question

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

Report

Raj_1562

Posts: 21
Registration date: Wednesday August 26, 2020
Status: Member
Last seen: October 13, 2020

- Updated on Sep 29, 2020 at 08:13 AM
Raj_1562

Posts: 21
Registration date: Wednesday August 26, 2020
Status: Member
Last seen: October 13, 2020

- 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

Grey out an cell and not allowed to enter depending on a cell
Grey out an entire row in Excel 2010 depending on a cell's value ✓ - Forum - Excel
If cell contains (multiple text criteria) then return (corresponding text criteria) ✓ - Forum - Excel
Excel if cell contains date then return value ✓ - Forum - Office Software
Keyboard not allowing me to type - How-To - Keyboard
Cell phone country code usa - How-To - Mobile

6 replies

Answer 2 / 6

TrowaD

Posts: 2688
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: January 7, 2021

452
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

Answer 3 / 6

Raj_1562

Posts: 21
Registration date: Wednesday August 26, 2020
Status: Member
Last seen: October 13, 2020

Sep 29, 2020 at 12:30 PM

Hi Trowa,

Great!

It's work good.

Thanks,
Raj

Answer 4 / 6

Raj_1562

Posts: 21
Registration date: Wednesday August 26, 2020
Status: Member
Last seen: October 13, 2020

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

Answer 5 / 6

Raj_1562

Posts: 21
Registration date: Wednesday August 26, 2020
Status: Member
Last seen: October 13, 2020

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

Answer 6 / 6

Raj_1562

Posts: 21
Registration date: Wednesday August 26, 2020
Status: Member
Last seen: October 13, 2020

Oct 1, 2020 at 12:02 PM

Hi Trowa,

Awesome!

Works as expected!!!

Regards,
Raj

Reply Ask a question

TrowaD · Accepted Answer · 2020-09-29T12:01:41-0400

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

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

6 replies

Your reply

Subscribe To Our Newsletter!