Worksheet event is not working in a particular sheet

ajo_5895

New Member
Joined
Dec 29, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
I have written code for the actions to be performed based on dropdown option that you are selecting.
if you select "Yes", the row from Columns B:N will high-light yellow. If you then type something else into any of the Columns B:N in an already high-lighted row(and then click away or Tab or move to another cell), the row color will immediately return to normal. Hence, an event has to happen in a cell for the code to execute. Sharing file which somewhat looks like this and all the columns depend on each other.

Ajo.xlsm

Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Columns("B:N")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
x = Target.Row

If Target.Value = "Yes" And WorksheetFunction.CountA(Range("C" & x).Resize(, 12)) = 0 Then
Range("C" & x).Resize(, 12).Interior.ColorIndex = 6
ElseIf Target.Value = "No" Then
Range("C" & x).Resize(, 12).Value = "N/A"
End If
If Target.Column <> 2 Then
Target.Interior.ColorIndex = xlNone
End If

End Sub

This code is working perfectly fine in the other sheets but not into my worksheet which has n number merged cells.
Can anybody please help me with this ASAP.

Thanks,
Anu
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Hello.
Try to run this macro apart:
VBA Code:
Sub enable_events()
Application.EnableEvents = True
End Sub

You probably have disable the events before.
 
Upvote 0
Hi tried doing that.. It just performs "No" part of conditional statements and nothing else
 
Upvote 0
First of all unmerge all the merged cells. Merged cells and macros most often don't get along with each other. Then try this macro:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    If Intersect(Target, Columns("B:N")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If Target.Column = 2 Then
        If Target.Value = "Yes" And WorksheetFunction.CountA(Range("C" & Target.Row).Resize(, 12)) = 0 Then
                Range("C" & Target.Row).Resize(, 12).Interior.ColorIndex = 6
        ElseIf Target.Value = "No" Then
                Range("C" & Target.Row).Resize(, 12).Value = "N/A"
                Range("C" & Target.Row).Resize(, 12).Interior.ColorIndex = xlNone
        End If
    Else
        Range("C" & Target.Row).Resize(, 12).Interior.ColorIndex = xlNone
    End If
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Shared some screenshots of the file where you might understand it at some level.

Hope this will help
 

Attachments

  • ss1.jpg
    ss1.jpg
    103.4 KB · Views: 3
  • ss2.jpg
    ss2.jpg
    90.4 KB · Views: 2
  • ss3.jpg
    ss3.jpg
    81 KB · Views: 3
Upvote 0
Peter, Mumps & Afonsomira.

Please note that this has been cross-posted here:-


@Ajo:-

Be aware that you did not write the code as per your opening post. Even your comments were extracted directly from mine!

You were given help towards a solution, including (as per Mumps advice) advice about merged cells. You abandoned the thread without acknowledgement.

How sad.
 
Upvote 0
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top