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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

afonsomira

Board Regular
Joined
Aug 11, 2020
Messages
62
Office Version
  1. 365
Platform
  1. Windows
Hello.
Try to run this macro apart:
VBA Code:
Sub enable_events()
Application.EnableEvents = True
End Sub

You probably have disable the events before.
 

ajo_5895

New Member
Joined
Dec 29, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Hi tried doing that.. It just performs "No" part of conditional statements and nothing else
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
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
 

ajo_5895

New Member
Joined
Dec 29, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Tried your code.. Still not able to fix it.
 

ajo_5895

New Member
Joined
Dec 29, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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: 1
  • ss2.jpg
    ss2.jpg
    90.4 KB · Views: 1
  • ss3.jpg
    ss3.jpg
    81 KB · Views: 1

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,374
Office Version
  1. 365
Platform
  1. Windows
Shared some screenshots
Trying to replicate from a picture is time-consuming and likely to not replicate accurately. What about a sample sheet via XL2BB or another shared file?
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,055
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,374
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,119
Messages
5,622,837
Members
415,934
Latest member
adstocking

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
Top