How to create a dynamic range variable

You need:

Range("B7:B" & noOfTests) = "Not Tested"

To elaborate on @Rory 's answer, or as to why your code doesn't work:

The double quotes " symbolize a String literal in VBA (and almost any other programming language).

The way a Range object works, is that you specify 1, or optionally 2 cells. Where Cell 1 is the starting cell (and ending cell if not specified) and Cell 2 is the ending cell, defining your selected range.

There are multiple ways of passing the arguments to a range, the most common is however to use the above-mentioned string literal. eg. Range("B7:B12") < note the quotes inside brackets

What you're however trying to do, is to dereference a variable's value inside a string literal. This is achieve in VBA via the ampersand & expression.

This what you're trying to do (if interpret could read a pseudocode)

Range("B7:B"<insert row value>) = "Not Tested"

What you're however doing instead is literally inputting your variable as part of your string, so:

Range("B7:BnoOfTests") = "Not Tested"

Obviously the interpeter is confused. BnoOfTests is not a common Cell referencing format and it doesn't know what to make of it. What you need to do is dereference your variable into a value as I hinted above, so the correct solution is:

Range("B7:B" & noOfTests) = "Not Tested"

Other good practice to avoid the string referencing confusion is to refer to the two cells that define your range. Much like I literally described above:

The way a Range object works, is that you specify 1, or optionally 2 cells. Where Cell 1 is the starting cell (and ending cell if not specified) and Cell 2 is the ending cell, defining your selected range

So to make sure you don't mess it up, you can use the following definition:

Range(Cells(7, "B"), Cells(noOfTests, "B")) = "Not Tested"

In my humble opinion, this is actually clearer transcription and furthermore, it is a lot less error prone!