Excel Macro Variable lines in spreadsheet

[BettyB]

I am trying to do my 1st macro ever and my spreadsheet will have varying number of lines each time. I can get the macro to work but it runs for the number of lines (16) when I recorded the macro instead of the number of lines actually in the new version of the spreadsheet now 31 rows. I have read about Do loops but do not understand how to write it.

Can anyone help me with the macro so that the number of lines is variable?

Here is my macro logic
Sub MacroTest3()
'
' MacroTest3 Macro
'
'
Range("D2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D16")
Range("D2:D16").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=3.53"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

 

[Airfix9]

If you can post your code, I'll give you some pointers to (a) tidy it up and (b) create it for as many rows as is required.

 

[BettyB]

Thanks Airfix9 I pasted in my macro logic. All I am doing is multiplying col 2 x 3 then conditional formatting if value in D is > 3

 

[Airfix9]

Try this, if anything is not clear, holler:

Sub MacroTest3()
'
' MacroTest3 Macro
'
'
    Dim r As Long ' this sets up a variable which will determine the number of rows
    
    Range("D2").FormulaR1C1 = "=RC[-2]*RC[-1]" ' selecting just wastes time, so we can just put the formula straight in

'we're now going to create a quick loop to run through all the cells in D, where there is a corresponding value in C

    r = 2 ' our starting row

    Do Until Cells(r, 3).Value = vbNullString 'keep going until C? = nothing

        Cells(r, 4).FormulaR1C1 = "=RC[-2]*RC[-1]" 'our formula

        r = r + 1 'go to the next row
    Loop ' and circle round to "Do"
    

'ok, now we'll conditionally format
'we now have r as being the first row with nothing in column C because the loop ended once r was bigger than the number of used rows
'so we need to go to the row before - just take one off r

    r = r - 1
    
    With Range("D2:D" & r) ' a "With" statement is a way to say that everything after pertains the this range, we preface everything that pertains to it with a period "."
        
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=3.53"
        .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Font ' you can have withs within withs
            .Color = -16752384
            .TintAndShade = 0
        End With
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 13561798
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
    End With
    
' that should work - try adding more rows
End Sub

 

[BettyB]

Thanks so much but I got a run-time error could I impose for a little more help. The error was runtime error "9" subscript out of range and the line below was highlighted when I did a debug.

.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

 

[Airfix9]

Comment it out using ' before it and see if that works. To be honest, I coded it blind, without testing. Might be unnecessary.

 

[BettyB]

Hi,
I commented the line out as suggested. The first part where it calculates the value continues to works great. No runtime error but the conditional formatting is not working at all.

 

[BettyB]

No wait it is working. It will not work if I am clicked in cell A1 but with my cursor on a blank cell further down the page the calculations and the conditional formatting worked! Thanks so very much for your help. This is beginning to make sense to me thanks to your tutoring.