Grey out an cell and not allowed to enter depending on a cell
Solved/Closed
Raj_1562
Posts
29
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 29 Registration date Wednesday August 26, 2020 Status Member Last seen August 29, 2022 - Oct 1, 2020 at 12:02 PM
Raj_1562 Posts 29 Registration date Wednesday August 26, 2020 Status Member Last seen August 29, 2022 - Oct 1, 2020 at 12:02 PM
Related:
- Grey out an cell and not allowed to enter depending on a cell
- How to enter @ in laptop - Guide
- Conditional formatting if cell contains text - Excel Forum
- If cell contains date then return value ✓ - Office Software Forum
- Whatsapp two grey ticks blocked - Guide
- If cell contains text then return value multiple conditions ✓ - Excel Forum
6 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Sep 29, 2020 at 12:01 PM
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:
Best regards,
Trowa
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
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Oct 1, 2020 at 11:24 AM
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:
Best regards,
Trowa
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
Raj_1562
Posts
29
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
August 29, 2022
Sep 29, 2020 at 12:30 PM
Sep 29, 2020 at 12:30 PM
Hi Trowa,
Great!
It's work good.
Thanks,
Raj
Great!
It's work good.
Thanks,
Raj
Raj_1562
Posts
29
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
August 29, 2022
Updated on Sep 29, 2020 at 01:07 PM
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
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
Didn't find the answer you are looking for?
Ask a question
Raj_1562
Posts
29
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
August 29, 2022
Updated on Sep 30, 2020 at 08:22 AM
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.
Thanks,
Raj
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
Raj_1562
Posts
29
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
August 29, 2022
Oct 1, 2020 at 12:02 PM
Oct 1, 2020 at 12:02 PM
Hi Trowa,
Awesome!
Works as expected!!!
Regards,
Raj
Awesome!
Works as expected!!!
Regards,
Raj
