I have followed the instructions in How To Create a Credit Card Payment sheet and everything is working except that the rows are not hidden if the value in column b is empty. This is because the tutorial uses the Sequence function and my version of Excel does not allow the sequence function. Therefore, the VBA code that is inserted in the spreadsheet is not working. Is there a way around this or am I just stuck with what I have? Great tutorial by the way.

Also, I can't seem to figure out how to display cells in red background if the value is a negative number.

I have followed the instructions in How To Create a Credit Card Payment sheet and everything is working except that the rows are not hidden if the value in column b is empty. This is because the tutorial uses the Sequence function and my version of Excel does not allow the sequence function. Therefore, the VBA code that is inserted in the spreadsheet is not working. Is there a way around this or am I just stuck with what I have? Great tutorial by the way.

Also, I can't seem to figure out how to display cells in red background if the value is a negative number.

Welcome to ExcelDemy Forum! Thanks for reaching out and sharing your problem.

You mentioned that your Excel version does not have the SEQUENCE function. Do not worry! I have come up with a solution that generates sequences based on the number of payments using an Excel VBA User-defined function. Besides, you wanted to learn how to display cells with a red background if the value is a negative number. To do so, I have developed the Worksheet_Calculate event procedure.

SOLUTION Overview:

Follow these steps:

Press Alt+F11 to open the VBA editor.

Click on Insert followed by Module.

Paste the following code in the module and save it:

Code:

Function GenerateSequence(ByVal numberOfPayments As Long) As Variant
Dim i As Long
Dim roundedPayments As Long
Dim sequence() As Variant
roundedPayments = Round(numberOfPayments, 0)
ReDim sequence(1 To roundedPayments, 1 To 1)
For i = 1 To roundedPayments
sequence(i, 1) = i
Next i
GenerateSequence = sequence
End Function

Open the VBA editor again and right-click on sheet1 from the Microsoft Excel Object list.

Later, click on View Code, paste the following code into the sheet module and save it:

Code:

Option Explicit
Private Sub Worksheet_Change(ByVal Cell_Target As Range)
Dim Cell_Range As Range
Application.ScreenUpdating = False
For Each Cell_Range In Range("B7:B100")
If Cell_Range.Value = "" Then
Cell_Range.EntireRow.Hidden = True
Else
Cell_Range.EntireRow.Hidden = False
End If
Next Cell_Range
Application.ScreenUpdating = True
End Sub
Private Sub Worksheet_Calculate()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In UsedRange
If IsNumeric(cell.Value) And cell.Value < 0 Then
cell.Interior.Color = RGB(255, 0, 0)
Else
cell.Interior.ColorIndex = xlColorIndexNone
End If
Next cell
Application.ScreenUpdating = True
End Sub

I hope you have found the solution helpful. I have also attached the solution workbook; good luck.

Thank you so much for your help. I am still having trouble thought and I imagine is that I am not doing it right. Which cell do you mean when you say "Select intended cell" in number 4. Also, even though I add the code for the module, there is no listed module when I view the macros. Is it not supposed to be there?

As for the spreadsheet you attached, I am getting an error message that says:

Thank you so much for your help. I am still having trouble thought and I imagine is that I am not doing it right. Which cell do you mean when you say "Select intended cell" in number 4. Also, even though I add the code for the module, there is no listed module when I view the macros. Is it not supposed to be there?

As for the spreadsheet you attached, I am getting an error message that says:

Thanks for thanking me. You were almost there. However, you faced several problems. Do not worry! I will try to explain the procedures more clearly.

Let us discuss your first problem. In my previous reply, I tried to refer to cell B5 by writing the intended cell (in step 4).

Now, let us solve the error you mentioned earlier. The "Ambiguous Name Detected: GenerateSequence" error means there is a problem because the workbook has two user-defined functions named "GenerateSequence."

So, assuming I already added the code for the GenerateSequence user-defined function in module 1 in my attached file, when you downloaded the file, you inserted another module and added the code again. It seems you are an Error like the following GIF:

Solution:

In module 1, keep the source code of the GenerateSequence user-defined function.

Keep the code responsible for raising events in the sheet module of sheet 1.

Hopefully, you understand why you are getting in trouble; good luck.

In following your second directions, I get the following error message when I try to copy the value in cell b5 to b24. I also get the same error message when I try to change any of the values in column H of your solved spreadsheet.View attachment 1358

In following your second directions, I get the following error message when I try to copy the value in cell b5 to b24. I also get the same error message when I try to change any of the values in column H of your solved spreadsheet.View attachment 1358

Perhaps the Excel Version is not the reason. The Worksheet_Calculate event is available in almost all versions of Excel VBA, including the most widely used versions, including Excel 2003 and onwards (2007, 2010, 2013, etc.).

Do not worry! Currently, I am investigating your problem. Hopefully, I will come up with an idea. Stay connected.

In following your second directions, I get the following error message when I try to copy the value in cell b5 to b24. I also get the same error message when I try to change any of the values in column H of your solved spreadsheet.View attachment 1358

Thanks for staying with ExcelDemy Forum. I have found the reason why you are having trouble. You are getting an error on the highlighted line because you are not correctly using the GenerateSequence user-defined function in cell B4. You do not need to copy the formula down to cell B24 using the Fill Handle or double-clicking. All you need to do is insert the formula in cell B4.

So, before following the previous 2nd instruction, you must generate the sequence in column B [in other words, before pasting codes of the event procedures (Worksheet_Change and Worksheet_Calculate) in the sheet module of sheet1].

I have set up another spreadsheet from the start and only added the vba code for the module before entering the vba code that goes into the Sheet1 area. When I enter the formula =GenerateSequence(H7) it only adds the number 1 in the first cell of the B column, directly under the heading Month. Any other ideas?

I have set up another spreadsheet from the start and only added the vba code for the module before entering the vba code that goes into the Sheet1 area. When I enter the formula =GenerateSequence(H7) it only adds the number 1 in the first cell of the B column, directly under the heading Month. Any other ideas?

Thanks for staying with ExcelDemy Forum! I have reviewed your problem and come up with a solution. I have made two Excel VBA Sub-procedures and called them in a Worksheet_Change event.

So, instead of using the GenerateSequence user-defined function, all you need to do is put the following code in the sheet module and save it. Later, go to the sheet and make changes to see an output like the following GIF:

Excel VBA Event Procedure:

Code:

Option Explicit
Public Sub PopulateValues()
Dim ws As Worksheet
Dim i As Integer
Dim numPayments As Integer
Dim currentRow As Integer
Set ws = ThisWorkbook.Sheets("Sheet1")
numPayments = Round(ws.Range("H7").Value)
currentRow = 5
For i = 1 To numPayments
ws.Cells(currentRow, 2).Value = i
currentRow = currentRow + 1
Next i
End Sub
Public Sub RemoveMonth()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
If lastRow >= 5 Then
ws.Range("B5:B" & lastRow).ClearContents
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
If Not Intersect(Target, ws.Range("H4:H8")) Is Nothing Then
Call RemoveMonth
Call PopulateValues
End If
Application.ScreenUpdating = False
Dim cell As Range
For Each cell In UsedRange
If IsNumeric(cell.Value) And cell.Value < 0 Then
cell.Interior.Color = RGB(255, 0, 0)
Else
cell.Interior.ColorIndex = xlColorIndexNone
End If
Next cell
Dim Cell_Range As Range
For Each Cell_Range In ws.Range("B7:B100")
If Cell_Range.Value = "" Then
Cell_Range.EntireRow.Hidden = True
Else
Cell_Range.EntireRow.Hidden = False
End If
Next Cell_Range
Application.ScreenUpdating = True
End Sub

I hope the solution will help you overcome your situation. I have attached the solution workbook; good luck.

I noticed that if I enter any information that requires a number of payments over 20, the month column (Column B) populates, but columns C, D and E do not populate beyond row 20. Can that be corrected and if so how do I do it?

I noticed that if I enter any information that requires a number of payments over 20, the month column (Column B) populates, but columns C, D and E do not populate beyond row 20. Can that be corrected and if so how do I do it?

Dear Afoster, Thanks for letting us know that the previously provided VBA Sub-Procedure and Event procedure worked perfectly on your end.

I have improved the file and fulfilled your requirements by dragging the existing formulas for the C, D, and E columns up to 100 rows. Please check the following:

I have attached the workbook. Stay connected. Good luck.

I noticed that if I enter any information that requires a number of payments over 20, the month column (Column B) populates, but columns C, D and E do not populate beyond row 20. Can that be corrected and if so how do I do it?

Dear Afoster, Thanks for letting us know that the previously provided VBA Sub-Procedure and Event procedure worked perfectly on your end.

I have improved the file and fulfilled your requirements by dragging the existing formulas for the C, D, and E columns up to 100 rows. Please check the following:

Dear Afoster, Thanks for letting us know that the previously provided VBA Sub-Procedure and Event procedure worked perfectly on your end.

I have improved the file and fulfilled your requirements by dragging the existing formulas for the C, D, and E columns up to 100 rows. Please check the following:

I noticed that if I enter any information that requires a number of payments over 20, the month column (Column B) populates, but columns C, D and E do not populate beyond row 20. Can that be corrected and if so how do I do it?

Thanks, it does work, but it does not delete the rows below the number of payments.

Excuse the delay in getting back to you...I see that the revised spreadsheet has 100 rows and I wanted to thank you for your help on this.