Hi everyone, below is the code I am using and for some reason every other time I change the numbers in rng5 and rng6 the loops will execute and run correctly but not every time (I change the number it runs correctly, i change the number again it doesnt run, i change it again and it runs correctly so on and so forth). I know its a problem with how I have my If statements or possibly for each loops set up but I think I am too close to the code and need an outside perspective. Maybe take a look at the "Set rng5/6..." statements in the If statements? Any help would be greatly appriciated, thanks in advance! - Matt

What the code is supposed to do is:

For each cell in the range (rng1/2)>

if cell value is between a certain numerical range >

then unhide/hide certain rows >

set the unhidden cell's formula to the given formula >

clear the contents of all the hidden cells >

set the range (rng1/2) = to the range of unhidden rows >

return to step 1 (for each cell in range (rng1/2)) >...

Code:

```
'Function attached to the Summary Sheet, Runs when a change is detected on the Summary Sheet.
Private Sub Worksheet_Calculate()
' Sets Variables.
Dim rng5 As Range
Dim rng6 As Range
Dim cell5 As Range
Dim cell6 As Range
Set rng5 = Range("F13")
Set rng6 = Range("F114")
' Stops the Excel Event Handlers from being called so that the function process and to prevent infinite loops.
Application.EnableEvents = False
' Production\Database\...
' Begin a For Each loop that executes for each cell (cell.Value) in the Range (rng) given above in variables.
For Each cell5 In rng5
' If statement to check if cell.Value is greater or equal to 18000.
If cell5.Value >= 18000 Then
' If conditions are met then the given Row Ranges are unhid (SQL01 through SQL19 directories).
Range("14:22").EntireRow.Hidden = False
Range("23:111").EntireRow.Hidden = True
Set rng5 = Range("F13:F22")
' Sets the given Ranges for SQL01 through SQL19 directories equal to the default formula given to calculate the size of that directory.
' Distributes the total size of the database evenly between all 10 directories SQL01 through SQL19.
Range("G13:G22, H13:H22, I13:I22, J13:J22, K13:K22, L13:L22, M13:M22, N13:N22, O13:O22, P13:P22").Formula = "=(CEILING(Worksheet!G$9*(1+Worksheet!G$10+Info!$B$14),5))/10"
' Clears contents of hidden cells.
Range("G23:G111, H23:H111, I23:I111, J23:J111, K23:K111, L23:L111, M23:M111, N23:N111, O23:O111, P23:P111").ClearContents
' If statement to check if cell.Value is between 16000 and 18000.
ElseIf cell5.Value >= 16000 And cell5.Value < 18000 Then
' If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL09 directories).
Range("14:21").EntireRow.Hidden = False
Range("22:111").EntireRow.Hidden = True
Set rng5 = Range("F13:F21")
' Sets the given Ranges for SQL01 through SQL09 directories equal to the default formula given to calculate the size of that directory.
' Distributes the total size of the database evenly between all 9 directories SQL01 through SQL09.
Range("G13:G21, H13:H21, I13:I21, J13:J21, K13:K21, L13:L21, M13:M21, N13:N21, O13:O21, P13:P21").Formula = "=(CEILING(Worksheet!G$9*(1+Worksheet!G$10+Info!$B$14),5))/9"
' Clears contents of hidden cells.
Range("G22:G111, H22:H111, I22:I111, J22:J111, K22:K111, L22:L111, M22:M111, N22:N111, O22:O111, P22:P111").ClearContents
' If statement to check if cell.Value is between 14000 and 16000.
ElseIf cell5.Value >= 14000 And cell5.Value < 16000 Then
' If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL08 directories).
Range("14:20").EntireRow.Hidden = False
Range("21:111").EntireRow.Hidden = True
Set rng5 = Range("F13:F20")
' Sets the given Ranges for SQL01 through SQL08 directories equal to the default formula given to calculate the size of that directory.
' Distributes the total size of the database evenly between all 8 directories SQL01 through SQL08.
Range("G13:G20, H13:H20, I13:I20, J13:J20, K13:K20, L13:L20, M13:M20, N13:N20, O13:O20, P13:P20").Formula = "=(CEILING(Worksheet!G$9*(1+Worksheet!G$10+Info!$B$14),5))/8"
' Clears contents of hidden cells.
Range("G21:G111, H21:H111, I21:I111, J21:J111, K21:K111, L21:L111, M21:M111, N21:N111, O21:O111, P21:P111").ClearContents
' If statement to check if cell.Value is between 12000 and 14000.
ElseIf cell5.Value >= 12000 And cell5.Value < 14000 Then
' If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL07 directories).
Range("14:19").EntireRow.Hidden = False
Range("20:111").EntireRow.Hidden = True
Set rng5 = Range("F13:F19")
' Sets the given Ranges for SQL01 through SQL07 directories equal to the default formula given to calculate the size of that directory.
' Distributes the total size of the database evenly between all 7 directories SQL01 through SQL07.
Range("G13:G19, H13:H19, I13:I19, J13:J19, K13:K19, L13:L19, M13:M19, N13:N19, O13:O19, P13:P19").Formula = "=(CEILING(Worksheet!G$9*(1+Worksheet!G$10+Info!$B$14),5))/7"
' Clears contents of hidden cells.
Range("G20:G111, H20:H111, I20:I111, J20:J111, K20:K111, L20:L111, M20:M111, N20:N111, O20:O111, P20:P111").ClearContents
' If statement to check if cell.Value is between 10000 and 12000.
ElseIf cell5.Value >= 10000 And cell5.Value < 12000 Then
' If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL06 directories).
Range("14:18").EntireRow.Hidden = False
Range("19:111").EntireRow.Hidden = True
Set rng5 = Range("F13:F18")
' Sets the given Ranges for SQL01 through SQL06 directories equal to the default formula given to calculate the size of that directory.
' Distributes the total size of the database evenly between all 6 directories SQL01 through SQL06.
Range("G13:G18, H13:H18, I13:I18, J13:J18, K13:K18, L13:L18, M13:M18, N13:N18, O13:O18, P13:P18").Formula = "=(CEILING(Worksheet!G$9*(1+Worksheet!G$10+Info!$B$14),5))/6"
' Clears contents of hidden cells.
Range("G19:G111, H19:H111, I19:I111, J19:J111, K19:K111, L19:L111, M19:M111, N19:N111, O19:O111, P19:P111").ClearContents
' If statement to check if cell.Value is between 8000 and 10000.
ElseIf cell5.Value >= 8000 And cell5.Value < 10000 Then
' If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL05 directories).
Range("14:17").EntireRow.Hidden = False
Range("18:111").EntireRow.Hidden = True
Set rng5 = Range("F13:F17")
' Sets the given Ranges for SQL01 through SQL05 directories equal to the default formula given to calculate the size of that directory.
' Distributes the total size of the database evenly between all 5 directories SQL01 through SQL05.
Range("G13:G17, H13:H17, I13:I17, J13:J17, K13:K17, L13:L17, M13:M17, N13:N17, O13:O17, P13:P17").Formula = "=(CEILING(Worksheet!G$9*(1+Worksheet!G$10+Info!$B$14),5))/5"
' Clears contents of hidden cells.
Range("G18:G111, H18:H111, I18:I111, J18:J111, K18:K111, L18:L111, M18:M111, N18:N111, O18:O111, P18:P111").ClearContents
' If statement to check if cell.Value is between 6000 and 8000.
ElseIf cell5.Value >= 6000 And cell5.Value < 8000 Then
' If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL04 directories).
Range("14:16").EntireRow.Hidden = False
Range("17:111").EntireRow.Hidden = True
Set rng5 = Range("F13:F16")
' Sets the given Ranges for SQL01 through SQL04 directories equal to the default formula given to calculate the size of that directory.
' Distributes the total size of the database evenly between all 4 directories SQL01 through SQL04.
Range("G13:G16, H13:H16, I13:I16, J13:J16, K13:K16, L13:L16, M13:M16, N13:N16, O13:O16, P13:P16").Formula = "=(CEILING(Worksheet!G$9*(1+Worksheet!G$10+Info!$B$14),5))/4"
' Clears contents of hidden cells.
Range("G17:G111, H17:H111, I17:I111, J17:J111, K17:K111, L17:L111, M17:M111, N17:N111, O17:O111, P17:P111").ClearContents
' If statement to check if cell.Value is between 4000 and 6000.
ElseIf cell5.Value >= 4000 And cell5.Value < 6000 Then
' If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL03 directories).
Range("14:15").EntireRow.Hidden = False
Range("16:111").EntireRow.Hidden = True
Set rng5 = Range("F13:F15")
' Sets the given Ranges for SQL01 through SQL03 directories equal to the default formula given to calculate the size of that directory.
' Distributes the total size of the database evenly between all 3 directories SQL01 through SQL03.
Range("G13:G15, H13:H15, I13:I15, J13:J15, K13:K15, L13:L15, M13:M15, N13:N15, O13:O15, P13:P15").Formula = "=(CEILING(Worksheet!G$9*(1+Worksheet!G$10+Info!$B$14),5))/3"
' Clears contents of hidden cells.
Range("G16:G111, H16:H111, I16:I111, J16:J111, K16:K111, L16:L111, M16:M111, N16:N111, O16:O111, P16:P111").ClearContents
' If statement to check if cell.Value is between 2000 and 4000.
ElseIf cell5.Value >= 2000 And cell5.Value < 4000 Then
' If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL02 directories).
Range("14:14").EntireRow.Hidden = False
Range("15:111").EntireRow.Hidden = True
Set rng5 = Range("F13:F14")
' Sets the given Ranges for SQL01 through SQL02 directories equal to the default formula given to calculate the size of that directory.
' Distributes the total size of the database evenly between all 2 directories SQL01 through SQL02.
Range("G13:G14, H13:H14, I13:I14, J13:J14, K13:K14, L13:L14, M13:M14, N13:N14, O13:O14, P13:P14").Formula = "=(CEILING(Worksheet!G$9*(1+Worksheet!G$10+Info!$B$14),5))/2"
' Clears contents of hidden cells.
Range("G15:G111, H15:H111, I15:I111, J15:J111, K15:K111, L15:L111, M15:M111, N15:N111, O15:O111, P15:P111").ClearContents
Else
' Hides the given Row Ranges on the Summary Sheet as default (SQL01 through SQL19).
Range("14:111").EntireRow.Hidden = True
' Sets the given Ranges for SQL01 through SQL19 directories equal to the default formula given to calculate the size of that directory.
Range("G13:P13").Formula = "=(CEILING(Worksheet!G$9*(1+Worksheet!G$10+Info!$B$14),5))"
' Clears contents of hidden cells.
Range("G14:G111, H14:H111, I14:I111, J14:J111, K14:K111, L14:L111, M14:M111, N14:N111, O14:O111, P14:P111").ClearContents
End If
Next
' Production\Backups\...
' Begin a For Each loop that executes for each cell (cell.Value) in the Range (rng) given above in variables.
For Each cell6 In rng6
' If statement to check if cell.Value is greater or equal to 18000.
If cell6.Value >= 18000 Then
' If conditions are met then the given Row Ranges are unhid (SQL01 through SQL19 directories).
Range("115:123").EntireRow.Hidden = False
Range("124:212").EntireRow.Hidden = True
Set rng6 = Range("F114:F123")
' Sets the given Ranges SQL01 through SQL10 Backup directories equal to the default formula given to calculate the size of that directory.
Range("G114:G123, H114:H123, I114:I123, J114:J123, K114:K123, L114:L123, M114:M123, N114:N123, O114:O123, P114:P123").Formula = "=(CEILING(IF(Worksheet!G$11 = ""Yes"",SUM(G$13:G$112) * Info!$B$6,SUM(G$13:G$112)),5))/10"
' Clears contents of hidden cells.
Range("G124:G212, H124:H212, I124:I212, J124:J212, K124:K212, L124:L212, M124:M212, N124:N212, O124:O212, P124:P212").ClearContents
' If statement to check if cell.Value is between 16000 and 18000.
ElseIf cell6.Value >= 16000 And cell6.Value < 18000 Then
' If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL09 directories).
Range("115:122").EntireRow.Hidden = False
Range("123:212").EntireRow.Hidden = True
Set rng6 = Range("F114:F122")
' Sets the given Ranges SQL01 through SQL09 Backup directories equal to the default formula given to calculate the size of that directory.
Range("G114:G122, H114:H122, I114:I122, J114:J122, K114:K122, L114:L122, M114:M122, N114:N122, O114:O122, P114:P122").Formula = "=(CEILING(IF(Worksheet!G$11 = ""Yes"",SUM(G$13:G$112) * Info!$B$6,SUM(G$13:G$112)),5))/9"
' Clears contents of hidden cells.
Range("G123:G212, H123:H212, I123:I212, J123:J212, K123:K212, L123:L212, M123:M212, N123:N212, O123:O212, P123:P212").ClearContents
' If statement to check if cell.Value is between 14000 and 16000.
ElseIf cell6.Value >= 14000 And cell6.Value < 16000 Then
' If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL08 directories).
Range("115:121").EntireRow.Hidden = False
Range("122:212").EntireRow.Hidden = True
Set rng6 = Range("F114:F121")
' Sets the given Ranges SQL01 through SQL08 Backup directories equal to the default formula given to calculate the size of that directory.
Range("G114:G121, H114:H121, I114:I121, J114:J121, K114:K121, L114:L121, M114:M121, N114:N121, O114:O121, P114:P121").Formula = "=(CEILING(IF(Worksheet!G$11 = ""Yes"",SUM(G$13:G$112) * Info!$B$6,SUM(G$13:G$112)),5))/8"
' Clears contents of hidden cells.
Range("G122:G212, H122:H212, I122:I212, J122:J212, K122:K212, L122:L212, M122:M212, N122:N212, O122:O212, P122:P212").ClearContents
' If statement to check if cell.Value is between 12300 and 14000.
ElseIf cell6.Value >= 12000 And cell6.Value < 14000 Then
' If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL07 directories).
Range("115:120").EntireRow.Hidden = False
Range("121:212").EntireRow.Hidden = True
Set rng6 = Range("F114:F120")
' Sets the given Ranges SQL01 through SQL07 Backup directories equal to the default formula given to calculate the size of that directory.
Range("G114:G120, H114:H120, I114:I120, J114:J120, K114:K120, L114:L120, M114:M120, N114:N120, O114:O120, P114:P120").Formula = "=(CEILING(IF(Worksheet!G$11 = ""Yes"",SUM(G$13:G$112) * Info!$B$6,SUM(G$13:G$112)),5))/7"
' Clears contents of hidden cells.
Range("G121:G212, H121:H212, I121:I212, J121:J212, K121:K212, L121:L212, M121:M212, N121:N212, O121:O212, P121:P212").ClearContents
' If statement to check if cell.Value is between 10000 and 12300.
ElseIf cell6.Value >= 10000 And cell6.Value < 12000 Then
' If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL06 directories).
Range("115:119").EntireRow.Hidden = False
Range("120:212").EntireRow.Hidden = True
Set rng6 = Range("F114:F119")
' Sets the given Ranges SQL01 through SQL06 Backup directories equal to the default formula given to calculate the size of that directory.
Range("G114:G119, H114:H119, I114:I119, J114:J119, K114:K119, L114:L119, M114:M119, N114:N119, O114:O119, P114:P119").Formula = "=(CEILING(IF(Worksheet!G$11 = ""Yes"",SUM(G$13:G$112) * Info!$B$6,SUM(G$13:G$112)),5))/6"
' Clears contents of hidden cells.
Range("G120:G212, H120:H212, I120:I212, J120:J212, K120:K212, L120:L212, M120:M212, N120:N212, O120:O212, P120:P212").ClearContents
' If statement to check if cell.Value is between 8000 and 10000.
ElseIf cell6.Value >= 8000 And cell6.Value < 10000 Then
' If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL05 directories).
Range("115:118").EntireRow.Hidden = False
Range("119:212").EntireRow.Hidden = True
Set rng6 = Range("F114:F118")
' Sets the given Ranges SQL01 through SQL05 Backup directories equal to the default formula given to calculate the size of that directory.
Range("G114:G118, H114:H118, I114:I118, J114:J118, K114:K118, L114:L118, M114:M118, N114:N118, O114:O118, P114:P118").Formula = "=(CEILING(IF(Worksheet!G$11 = ""Yes"",SUM(G$13:G$112) * Info!$B$6,SUM(G$13:G$112)),5))/5"
' Clears contents of hidden cells.
Range("G119:G212, H119:H212, I119:I212, J119:J212, K119:K212, L119:L212, M119:M212, N119:N212, O119:O212, P119:P212").ClearContents
' If statement to check if cell.Value is between 6000 and 8000.
ElseIf cell6.Value >= 6000 And cell6.Value < 8000 Then
' If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL04 directories).
Range("115:117").EntireRow.Hidden = False
Range("118:212").EntireRow.Hidden = True
Set rng6 = Range("F114:F117")
' Sets the given Ranges SQL01 through SQL04 Backup directories equal to the default formula given to calculate the size of that directory.
Range("G114:G117, H114:H117, I114:I117, J114:J117, K114:K117, L114:L117, M114:M117, N114:N117, O114:O117, P114:P117").Formula = "=(CEILING(IF(Worksheet!G$11 = ""Yes"",SUM(G$13:G$112) * Info!$B$6,SUM(G$13:G$112)),5))/4"
' Clears contents of hidden cells.
Range("G118:G212, H118:H212, I118:I212, J118:J212, K118:K212, L118:L212, M118:M212, N118:N212, O118:O212, P118:P212").ClearContents
' If statement to check if cell.Value is between 4000 and 6000.
ElseIf cell6.Value >= 4000 And cell6.Value < 6000 Then
' If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL03 directories).
Range("115:116").EntireRow.Hidden = False
Range("117:212").EntireRow.Hidden = True
Set rng6 = Range("F114:F116")
' Sets the given Ranges SQL01 through SQL03 Backup directories equal to the default formula given to calculate the size of that directory.
Range("G114:G116, H114:H116, I114:I116, J114:J116, K114:K116, L114:L116, M114:M116, N114:N116, O114:O116, P114:P116").Formula = "=(CEILING(IF(Worksheet!G$11 = ""Yes"",SUM(G$13:G$112) * Info!$B$6,SUM(G$13:G$112)),5))/3"
' Clears contents of hidden cells.
Range("G117:G212, H117:H212, I117:I212, J117:J212, K117:K212, L117:L212, M117:M212, N117:N212, O117:O212, P117:P212").ClearContents
' If statement to check if cell.Value is between 2000 and 4000.
ElseIf cell6.Value >= 2000 And cell6.Value < 4000 Then
' If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL02 directories).
Range("115:115").EntireRow.Hidden = False
Range("116:212").EntireRow.Hidden = True
Set rng6 = Range("F114:F115")
' Sets the given Ranges SQL01 through SQL02 Backup directories equal to the default formula given to calculate the size of that directory.
Range("G114:G115, H114:H115, I114:I115, J114:J115, K114:K115, L114:L115, M114:M115, N114:N115, O114:O115, P114:P115").Formula = "=(CEILING(IF(Worksheet!G$11 = ""Yes"",SUM(G$13:G$112) * Info!$B$6,SUM(G$13:G$112)),5))/2"
' Clears contents of hidden cells.
Range("G116:G212, H116:H212, I116:I212, J116:J212, K116:K212, L116:L212, M116:M212, N116:N212, O116:O212, P116:P212").ClearContents
Else
' Hides the given Row Ranges on the Summary Sheet as default (SQL01 through SQL19).
Range("115:212").EntireRow.Hidden = True
' Sets the given Ranges SQL01 through SQL19 Backup directories equal to the default formula given to calculate the size of that directory.
Range("G114:P114").Formula = "=(CEILING(IF(Worksheet!G$11 = ""Yes"",SUM(G$13:G$112) * Info!$B$6,SUM(G$13:G$112)),5))"
' Clears contents of hidden cells.
Range("G115:G212, H115:H212, I115:I212, J115:J212, K115:K212, L115:L212, M115:M212, N115:N212, O115:O212, P115:P212").ClearContents
End If
Next
' Terminates the loop back to the beginning of the function.
Application.EnableEvents = True
End Sub
```

What the code is supposed to do is:

For each cell in the range (rng1/2)>

if cell value is between a certain numerical range >

then unhide/hide certain rows >

set the unhidden cell's formula to the given formula >

clear the contents of all the hidden cells >

set the range (rng1/2) = to the range of unhidden rows >

return to step 1 (for each cell in range (rng1/2)) >...

Last edited: