Need help with worksheet/conditional formatting/macros please

A

Aphten

New Member
Joined
Jul 24, 2024
Messages
9
Office Version
  1. 2021
Platform
  1. Windows
Hoping someone can help me. Any and all help is appreciated. I have a worksheet that has multiple macros and conditional formatting in addition to utilizing worksheet_change and worksheet_selectionchange. I use this form for work and it is designed to be used as a worksheet that I can fill out repeatedly. I have one worksheet labeled "blank form" and then I just copy this each time I need to fill out another one while of course giving it a new name. Each one of these worksheets stay within the workbook. This is going to be hard to explain because I am not exactly Excel-savvy, but I have macros set up as buttons on the blank form (Admission, 5-day, IPA, etc.), to slightly modify this form as there are different iterations of this form. The only modifications are that some questions don't need to be answered so the macros are designed to fill with black to essentially get rid of things that don't need to be answered.

The first screen shot is my "blank form"
1739902570998.png

The second screen shot is my "modified" form using a macro.
1739902867424.png


Most of the conditional formatting is to either change the text from invisible (format---> ;;; ) to not invisible (format--->text) but there are a couple in there that will change the fill. I have helper columns that are set up to equal either true or false and these are what are being used with the conditional formatting.

I am using worksheet_selectionchange for select cells to equal something based on another cell. I have included a portion of this code. While it is not the entire code, it is just the same code repeated for different cells.

VBA Code: 
For Each RNG In Range("AG99")
If Range("AO99") = "N" Then
RNG.Value = "N"
End If
Next RNG
For Each RNG In Range("AG99")
If Range("AO99") = "" Then
RNG.Value = ""
End If
Next RNG


I am using worksheet_change to utilize Ucase and ProperCase. Everything was working great until I added in this code. Once I did, excel started crashing and I get a message stating that some conditional formatting was removed. More than that actually happens though. My macros disappear. I can still see my personal macrobook but my macros are gone and if I hadn't saved the code in a word file, I would have been back to step one. All the previous forms that I have already completed get messed up too. From what I can tell, the conditional formatting that has been deemed a problem and removed, is applied to all the sheets in this workbook, which include forms I have already filled out. It's a hot mess and it messes everything up! I have included my code below.

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub

    On Error Resume Next

    If Not Intersect(Target, Union(Range( _
        "AC78:AD80,AK77:AL80,AS77:AT80,L80,R80,C81:C82,C86:C94,K86:K94,AG81,AG83,Y82:Y84,AY82:AZ82,AY83:AZ83,G96:H96,G98:H98,AG88:AN88,AG90:AN92,AG95:AN95,AG97:AN98,Y86,AG86,AO86,AO87:AZ99,H17,P17:P19,R16:S16,T16:T19,AA17:AA18,AI16:AM16,G21:L21,G24:L24,S20:S22" _
        ), Range( _
        "AA20:AA22,AH21:AH25,AP21:AP24,AP28:AP31,AU32:AZ32,AX38:AZ38,AY44:AZ44,AP45:AP47,AP49:AP54,AY48:AZ48,AP56:AQ59,AP60:AQ60,AY64:AZ64,AP65,AU65,C38:C65,V38:V65,K66:N66,C67:C70,Y71:AR74,AW71:AZ74,C72:C79,AD77,AU86,AJ67:AO68" _
        ))) Is Nothing Then

        Application.ScreenUpdating = False
        Application.EnableEvents = False

        Target = UCase(Target)

        Application.EnableEvents = True

    End If
        If Not Intersect(Target, Range("J13")) Is Nothing Then

        Application.EnableEvents = False

        Target = StrConv(Target, vbProperCase)

        Application.ScreenUpdating = True
        Application.EnableEvents = True

    End If

    On Error GoTo 0

End Sub

Is this interfering with the conditional formatting? Or is it the other way around? Can you not have conditional formatting apply to the same cells that are in a worksheet_change? Or is there something wrong with my code. Everything I know about macros/VBA is self-taught which is probably pretty apparent so feel free to tell me if I did anything wrong or if there is a way to simplify this. Everything was working without issue until I added in the above code to convert select ranges to Ucase of ProperCase.

Apologies if I didn't post something correctly or used an incorrect term. This is still pretty new to me so I am not aware of common courtesies when posting to this kind of forum.
 
Sort by date Sort by votes
It sounds like your workbook is a bit of a mess now. I hope you can revert to a backup. It is possible you also have some redundant conditional formatting that needs to be cleaned out.

There is no reason in principle why conditional formatting and code can't co-exist.

But there are a couple of issue with the code posted ...

Why are you using Worksheet_Selection_Change? You need to run this code snippet only if a value is entered into AO99 (which you can test using Sub Worksheet_Change), rather than every time the selection changes.

Every change the code makes, e.g.
VBA Code: 
RNG.Value = "N"
will trigger Sub Worksheet_Change unless you wrap it like this:

Code: 
Application.EnableEvents = False
'code that will trigger Sub Worksheet_Change, e.g.
Rng.Value = "N"
'...
Application.EnableEvents = True

So you could do this more succinctly like this:

Code: 
Select Case Range("AO99").Value
Case "N", ""
    Application.EnableEvents = False
    Range("AG99").Value = Range("AO99").Value
    Application.EnableEvents = True
End Select
End Sub
 
Upvote 0
StephenCrump said:
It sounds like your workbook is a bit of a mess now. I hope you can revert to a backup. It is possible you also have some redundant conditional formatting that needs to be cleaned out.

There is no reason in principle why conditional formatting and code can't co-exist.

But there are a couple of issue with the code posted ...

Why are you using Worksheet_Selection_Change? You need to run this code snippet only if a value is entered into AO99 (which you can test using Sub Worksheet_Change), rather than every time the selection changes.

Every change the code makes, e.g.
VBA Code: 
RNG.Value = "N"
will trigger Sub Worksheet_Change unless you wrap it like this:

Code: 
Application.EnableEvents = False
'code that will trigger Sub Worksheet_Change, e.g.
Rng.Value = "N"
'...
Application.EnableEvents = True

So you could do this more succinctly like this:

Code: 
Select Case Range("AO99").Value
Case "N", ""
    Application.EnableEvents = False
    Range("AG99").Value = Range("AO99").Value
    Application.EnableEvents = True
End Select
End Sub
Click to expand...
Thank you for the response.

1. When you say redundant, do you mean I have lines of conditional formatting that have overlapping cells, lines that are somehow repeating themselves or contradicting another conditional formatting line, or conditional formatting that is contradicting or overlapping VBA coding?

2. I guess I didn't educate myself well enough. I was under the impression the Worksheet_SelectionChange only triggered when a select cell (the ones in the code) was changed, but it triggers every time anything on the sheet is changed?

3. Please remember that I am a total newbie. With the suggestion for more succinct coding:

What does this do?

Code: 
Case "N", ""

And what does this do?

Code: 
Range("AG99").Value = Range("AO99").Value

I had two columns in my worksheet. Let's call one Monday and the other Sun-Sat. If a row in Sun-Sat is marked No (N), then logically the corresponding row in the Monday column would be No (N). The code that I had in Worksheet_SelectionChange would automatically put an N in the Monday column if an N was put in the Sun-Sat column, and then I had multiple rows doing the same thing. Is that what your suggested code is doing as well?

Yes, my worksheet is a mess. 😆 And yes, I did make a backup with no VBA or conditional formatting, so I essentially have a clean slate. I really appreciate your help!
 
Upvote 0
Conditional formatting (CF) creates overheads. Part of the problem may be that you have too much conditional formatting. For example, any copying of worksheets/cells with conditional formatting will copy the conditional formatting. It was just a suggestion that you check this and clean any duplication or unnecessary CF that may have accumulated.

Aphten said:
I was under the impression the Worksheet_SelectionChange only triggered when a select cell (the ones in the code) was changed
Click to expand...
No, you specify the selected cells in the Sub to limit the code's action. I've shown Worksheet_Change below. You can do similar in Worksheet_SelectionChange, but you're only interested in changes to the Sat-Sun cells, not merely the selection of those cells:

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    'This Sub triggers every time there is a change in the worksheet
    '(This means a value(s) has been entered by the user or by code, not necessarily a changed value on what was there before)
    'Note that a change in a formula cell's value does not trigger this Sub. 
    
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        'This code block will only execute if A1 is changed
    End If

    If Not Intersect(Target, Range("B1:B3")) Is Nothing Then
        'This code block will only execute if B1:B3 is changed
    End If
    
    'etc
    
End Sub

The Select Case structure allows you to deal with various possibilities without using If. Compare:

Code: 
Select Case x
Case 1
    'do something
Case 2, 3
    'do something
Case 4
    'do something
Case Else
    MsgBox "Invalid x"
End Select

If x = 1 Then
    'do something
ElseIf x = 2 Or x = 3 Then
    'do something
ElseIf x = 4 Then
    'do something
Else
    MsgBox "Invalid x"
End If

Aphten said:
And what does this do?

Range("AG99").Value = Range("AO99").Value
Click to expand...
Puts the value of AO99 into AG99.

Code: 
'Effectively, rather than saying
If Range("AO99").Value = "N" Then Range("AG99").Value = "N"
If Range("AO99").Value = "" Then Range("AG99").Value = ""
'etc

'I'd say
r = Range("AO99").Value
If r = "N" Or r = "" Then Range("AG99").Value = r
 
Upvote 0
StephenCrump said:
Conditional formatting (CF) creates overheads. Part of the problem may be that you have too much conditional formatting. For example, any copying of worksheets/cells with conditional formatting will copy the conditional formatting. It was just a suggestion that you check this and clean any duplication or unnecessary CF that may have accumulated.


No, you specify the selected cells in the Sub to limit the code's action. I've shown Worksheet_Change below. You can do similar in Worksheet_SelectionChange, but you're only interested in changes to the Sat-Sun cells, not merely the selection of those cells:

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    'This Sub triggers every time there is a change in the worksheet
    '(This means a value(s) has been entered by the user or by code, not necessarily a changed value on what was there before)
    'Note that a change in a formula cell's value does not trigger this Sub.
   
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        'This code block will only execute if A1 is changed
    End If

    If Not Intersect(Target, Range("B1:B3")) Is Nothing Then
        'This code block will only execute if B1:B3 is changed
    End If
   
    'etc
   
End Sub

The Select Case structure allows you to deal with various possibilities without using If. Compare:

Code: 
Select Case x
Case 1
    'do something
Case 2, 3
    'do something
Case 4
    'do something
Case Else
    MsgBox "Invalid x"
End Select

If x = 1 Then
    'do something
ElseIf x = 2 Or x = 3 Then
    'do something
ElseIf x = 4 Then
    'do something
Else
    MsgBox "Invalid x"
End If


Puts the value of AO99 into AG99.

Code: 
'Effectively, rather than saying
If Range("AO99").Value = "N" Then Range("AG99").Value = "N"
If Range("AO99").Value = "" Then Range("AG99").Value = ""
'etc

'I'd say
r = Range("AO99").Value
If r = "N" Or r = "" Then Range("AG99").Value = r
Click to expand...
Thank you for all the info!

So I removed all of the CF with the plan to put the rules back in slowly to see if I can pinpoint the problem child.

I also used your suggested code:

Code: 
Select Case Range("AO99").Value
Case "N", ""
    Application.EnableEvents = False
    Range("AG99").Value = Range("AO99").Value
    Application.EnableEvents = True
End Select
End Sub

When I put "N" in AO99 then AG99 = "N", but when I delete the "N" in AO99 it takes ages (10+ seconds) for the "N" in AG99 to be removed. Sometimes it doesn't even seem to run so the "N" in AG99 remains. I have played around with changing
Code: 
 Case "N",""
to
Code: 
Case "N","C"
and it seems to run just fine. It only seems to lag or not work at all when "" is used. Any ideas?
 
Upvote 0
Aphten said:
Thank you for all the info!

So I removed all of the CF with the plan to put the rules back in slowly to see if I can pinpoint the problem child.

I also used your suggested code:

Code: 
Select Case Range("AO99").Value
Case "N", ""
    Application.EnableEvents = False
    Range("AG99").Value = Range("AO99").Value
    Application.EnableEvents = True
End Select
End Sub

When I put "N" in AO99 then AG99 = "N", but when I delete the "N" in AO99 it takes ages (10+ seconds) for the "N" in AG99 to be removed. Sometimes it doesn't even seem to run so the "N" in AG99 remains. I have played around with changing
Code: 
 Case "N",""
to
Code: 
Case "N","C"
and it seems to run just fine. It only seems to lag or not work at all when "" is used. Any ideas?
Click to expand...
I played with it some more and ended up removing all the previous coding I had, which I have added below. After doing so, your code worked perfect without any issues, so this tells me there must be something wrong with the code I removed. Do you see anything wrong with it or have any suggestions? As you can see, I have quite a few ranges that get changed to UCase and one cell that changes into ProperCase. The following code seems to run fine by itself and even ran fine when I added your suggested code, but then your suggested code doesn't work consistently.
Code: 
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next

        If Not Intersect(Target, Union(Range( _
        "AC78:AD80,AK77:AL80,AS77:AT80,L80,R80,C81:C82,C86:C94,K86:K94,AG81,AG83,Y82:Y84,AY82:AZ82,AY83:AZ83,G96:H96,G98:H98,AG88:AN88,AG90:AN92,AG95:AN95,AG97:AN98,Y86,AG86,AO86,AO87:AZ99,H17,P17:P19,R16:S16,T16:T19,AA17:AA18,AI16:AM16,G21:L21,G24:L24,S20:S22" _
        ), Range( _
        "AI13,AK30,AK32,AA20:AA22,AH21:AH25,AP21:AP24,AP28:AP31,AU32:AZ32,AX38:AZ38,AY44:AZ44,AP45:AP47,AP49:AP54,AY48:AZ48,AP56:AQ59,AP60:AQ60,AY64:AZ64,AP65,AU65,C38:C65,V38:V65,K66:N66,C67:C70,Y71:AR74,AW71:AZ74,C72:C79,AD77,AU86,AJ67:AO68" _
        ))) Is Nothing Then

    Application.ScreenUpdating = False
    Application.EnableEvents = False
        Target = UCase(Target)
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
End If

If Not Intersect(Target, Range("J13")) Is Nothing Then

    Application.ScreenUpdating = False
    Application.EnableEvents = False
        Target = StrConv(Target, vbProperCase)
    Application.ScreenUpdating = True
    Application.EnableEvents = True

End If
 
Upvote 0
Good to hear you're making progress! The latest code you've posted looks fine for what you're probably doing with it. But a couple of observations:

1. There's a place for using On Error Resume Next, e.g. where you know a code line may error, and you want to deal with that specific error, and then reset On Error GoTo 0. But it's not good practice to put it at the start of a routine, in case you've written sloppy code that might error (who knows where and with what consequences?).

2. If you want your code to allow for more than one cell being changed (Target.Cells.Count > 1), you'd write it like this:

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range, r As Range
    
    Set rng = Intersect(Target, Union(Range("AC78:AD80,AK77:AL80,AS77:AT80,L80,R80,C81:C82,C86:C94,K86:K94,AG81,AG83,Y82:Y84,AY82:AZ82,AY83:AZ83,G96:H96,G98:H98,AG88:AN88,AG90:AN92,AG95:AN95,AG97:AN98,Y86,AG86,AO86,AO87:AZ99,H17,P17:P19,R16:S16,T16:T19,AA17:AA18,AI16:AM16,G21:L21,G24:L24,S20:S22"), _
        Range("AI13,AK30,AK32,AA20:AA22,AH21:AH25,AP21:AP24,AP28:AP31,AU32:AZ32,AX38:AZ38,AY44:AZ44,AP45:AP47,AP49:AP54,AY48:AZ48,AP56:AQ59,AP60:AQ60,AY64:AZ64,AP65,AU65,C38:C65,V38:V65,K66:N66,C67:C70,Y71:AR74,AW71:AZ74,C72:C79,AD77,AU86,AJ67:AO68")))
    
    If Not rng Is Nothing Then
        For Each r In rng
            'deal with r
        Next r
    End If

End Sub
3. It's not usual to check Target.HasFormula in a Worksheet_Change Sub. The Sub won't trigger if a formula value changes - unless it's due to a change in a cell(s) elsewhere in the worksheet, in which case Target will refer to that cell(s).

(If Target.HasFormula is TRUE, it means the user is adding/editing a formula, which presumably they shouldn't be doing).

Aphten said:
When I put "N" in AO99 then AG99 = "N", but when I delete the "N" in AO99 it takes ages (10+ seconds) for the "N" in AG99 to be removed. Sometimes it doesn't even seem to run so the "N" in AG99 remains.
Click to expand...
It's hard to know what's happening here, and odd that it's just the blank "" causing the problem.

Presumably this code is in the same Worksheet_Change Sub, and there is other code here as well? I'd start by putting a couple of breakpoints in - at the top of the Sub, and on the problematic line, trigger a change to that cell, and step through the code line by line to see what's happening and how long it takes.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
VBA help...Excel keeps crashing
Replies
2
Views
131
Peter_SSs
Peter_SSs
Ramadan
  • Solved
My code to capitalize first letter duplicates the letter
Replies
9
Views
114
Ramadan
Ramadan
Ramadan
  • Locked
  • Solved
VBA code to add current date when the sheet receive new update
Replies
7
Views
324
Peter_SSs
Peter_SSs
S
  • Solved
event code to change to uppercase for 2 columns
Replies
4
Views
67
still learning
S
Dazzawm
  • Question Question
Help With Worksheet Change Code
2
Replies
12
Views
134
Dazzawm
Dazzawm

Forum statistics

Threads
1,226,795
Messages
6,193,047
Members
453,772
Latest member
aastupin

Share this page

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