Worksheet_Change(ByVal Target as Range) with multiple If statements

SpencerRichman

New Member
Joined
Feb 15, 2013
Messages
33
Office Version
  1. 2021
Platform
  1. Windows
I have the code below which contains two separate IF - Else statements. My simple question is this: When Cell C50 changes, will this code run and make the changes based on the current value of cell C50 AND at the same time, will it make changes based on the current value of cell C53? Or will the changes related to cell C53 ONLY happen when the value of cell C53 changes? In other words, are these two IF-Else statements independent of one another, or will a change in Cell C50 OR Cell C53 trigger them both to run?

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("C50") = "Basic" Then
Sheets("Software").Range("F84:G88").Interior.ColorIndex = 2
Else
If Range("C50") = "IQ Mid-Level" Then
Sheets("Software").Range("E84:E88").Interior.ColorIndex = 2
End If
End If

If Range("C53") = "1-5 Bundle" Then
Sheets("Software").Rows("93:93").Hidden = False
Else
If Range("C53") = "6-10 Bundle" Then
Sheets("Software").Rows("93:93").Hidden = True
End If
End If

End Sub
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
The way you have written this, any change in any cell will cause this sub to run and evaluate both C50 and C53 to take any appropriate action.

I would makes two changes to this code: 1) run only if the cell that changed was C50 or C53, and 2) use ElseIf instead of cascading If

I also recommend marking your code with code tags to preserve spacing and make it more readable:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

   If Not Intersect(Target, Range("C50")) Is Nothing Then
  
      If Range("C50") = "Basic" Then
         Sheets("Software").Range("F84:G88").Interior.ColorIndex = 2
      ElseIf Range("C50") = "IQ Mid-Level" Then
         Sheets("Software").Range("E84:E88").Interior.ColorIndex = 2
      End If
  
   ElseIf Not Intersect(Target, Range("C53")) Is Nothing Then

      If Range("C53") = "1-5 Bundle" Then
         Sheets("Software").Rows("93:93").Hidden = False
      ElseIf Range("C53") = "6-10 Bundle" Then
         Sheets("Software").Rows("93:93").Hidden = True
      End If
     
   End If

End Sub
 
Upvote 0
Solution
I just noticed that if you change C50 to "Basic", then change it again to "IQ Mid-Level", then both F84:G88 and E84:E88 end up being filled with colorindex 2. Is that what is intended?
 
Upvote 0
Thank you Jeff. I only copied a part of the code to make the example easier, so no worries about both ranges being filled with colorindex 2.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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