Saving the address of specific cells to change them later

Question

I'm using Excel VBA to try to solve the following problem:

In Column A, I'm given a list of 42 countries. In Column D, I'm given the price of a Big Mac in that country in USD. Row 1 has headers, so the data starts at Row 2. I need to build a Macro that allows the user to input 2 countries (Country1 and Country2), will loop through column A to find the countries the user put in, and their corresponding prices. It should save the cell location of the country into some variable, and the Price as just a number. If the Price of Country1 is greater than that of country 2, the name of Country1 should have a font color of green, and Country2 a font color of red. And vice versa.

Right now, the error I'm getting is "Object Variable or With Block Variable not set" for my variable TheCell.

In case you want to test it, here is the top of the sheet:

I've tried to play around with what I dimmed TheCell as. I tried making it a variant, but that wouldn't work. I'm pretty sure Range is correct, because it's the actual cell.

Sub CountryComparison()

Dim Counter As Integer
Dim Country1 As String
Dim Country2 As String
Dim TheCell As Range
Dim Price1Cell As Range
Dim Price2Cell As Range
Dim Price1 As Single
Dim Price2 As Single

'The user inputs what countries they want to compare

Country1 = InputBox("Enter Country 1")
Country2 = InputBox("Enter Country 2")

'We are starting at row 2, column 1. Since we're going to check every row, I'm making counter a variable so that I can continuously add 1 to it after every loop.

Counter = 2
TheCell = Cells(Counter, 1)

'Here's my loop. It will select TheCell, and if it contains the name of Country1, then it will save that cell as Price1Cell (to be used later), and save the price of a Big Mac in that country (also to be used later). It does the same thing for Country2. And if neither is a match, it goes on to the next row. Since there are 42 rows, it does this until Counter is greater than 43 (maybe it should be until greater than 42, but that shouldn't matter.)

Do
    TheCell.Select
    If ActiveCell.Value = Country1 Then
    Set Price1Cell = Range(ActiveCell.Address)
    Price1 = ActiveCell.Offset(0, 3).Value
    End If

    If ActiveCell.Value = Country2 Then
    Set Price2Cell = Range(ActiveCell.Address)
    Price2 = ActiveCell.Offset(0, 3).Value
    End If

    Counter = Counter + 1

Loop Until Counter > 43

'Here's the final point. If Country1's price is greater than Country2's Price, then Country1 should be colored red and Country2 green. And vice-versa.

If Price1 > Price2 Then
    Price1Cell.Font.Color = vbRed
    Price2Cell.Font.Color = vbGreen
End If

If Price2 > Price1 Then
    Price1Cell.Font.Color = vbGreen
    Price2Cell.Font.Color = vbRed
End If


End Sub

Answer 1

You can try the below:

Option Explicit

Sub test()

    Dim Country1 As String, Country2 As String
    Dim LastRow As Long
    Dim Position1 As Range, Position2 As Range
    Dim Price1 As Double, Price2 As Double

        Country1 = "Italy" '<- Testing name
        Country2 = "Cyprus" '<- Testing name
      With ThisWorkbook.Worksheets("Sheet1") '<- Change to fit your needs

        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        Set Position1 = .Range("A2:A" & LastRow).Find(Country1)

            If Not Position1 Is Nothing Then
                MsgBox "Country appears in: " & vbNewLine & "Column:" & Position1.Column & vbNewLine & "Row:" & Position1.Row & vbNewLine & "Full Address:" & Position1.Address & vbNewLine & "Price:" & .Range("D" & Position1.Row).Value
                Price1 = .Range("D" & Position1.Row).Value
            Else
                MsgBox "Country & Price1 not found."

            End If

        Set Position2 = .Range("A2:A" & LastRow).Find(Country2)

            If Not Position2 Is Nothing Then
                MsgBox "Country appears in: " & vbNewLine & "Column:" & Position2.Column & vbNewLine & "Row:" & Position2.Row & vbNewLine & "Full Address:" & Position2.Address & vbNewLine & "Price:" & .Range("D" & Position2.Row).Value
                Price2 = .Range("D" & Position2.Row).Value
            Else
                MsgBox "Country not & Price2 found."
            End If

            If Not Position1 Is Nothing And Not Position2 Is Nothing Then
                If Price1 > Price2 Then
                    .Range("D" & Position1.Row).Font.Color = vbRed
                    .Range("D" & Position2.Row).Font.Color = vbGreen
                End If

                If Price2 > Price1 Then
                    .Range("D" & Position1.Row).Font.Color = vbGreen
                    .Range("D" & Position2.Row).Font.Color = vbRed
                End If
            End If

    End With

End Sub

Answer 2

Cell Range for Later Formatting

Option Explicit

Sub CountryComparison()

    Const cCountry As Variant = "A"   ' Country Column Letter/Number
    Const cPrice As Variant = "D"     ' Price Column Letter/Number
    Const cFR As Long = 2             ' First Row Number
    Const cLR As Long = 42            ' Last Row Number

    Dim Country1 As String    ' 1st Country
    Dim Country2 As String    ' 2nd Country
    Dim TheCell As Range      ' Current Cell Range
    Dim Price1Cell As Range   ' 1st Price Cell Range
    Dim Price2Cell As Range   ' 2nd Price Cell Range
    Dim Price1 As Double      ' 1st Price
    Dim Price2 As Double      ' 2nd Price
    Dim i As Long             ' Row Counter

    ' The user inputs what countries they want to compare.
    Country1 = InputBox("Enter Country 1")
    Country2 = InputBox("Enter Country 2")

    ' Here's my loop. It will select TheCell, and if it contains the name of
    ' Country1, then it will save that cell as Price1Cell (to be used later),
    ' and save the price of a Big Mac in that country (also to be used later).
    ' It does the same thing for Country2. And if neither is a match, it goes
    ' on to the next row. Since there are 42 rows, it does this until Counter
    ' is greater than 43 (maybe it should be until greater than 42, but that
    ' shouldn't matter.)

    ' The Last Row (LR) is usually calculated from the bottom like this:
    'LR = Cells(Rows.Count, cCountry).End(xlUp).Row
    ' If you want to adopt this, just change cLR to LR in the first lines
    ' of the For Next loops and delete cLR in the constants section and
    ' add the declaration: Dim LR as Long

    ' Loop through cells (rows) of Country Column.
    For i = cFR To cLR
        ' Create a reference to Current Cell in Country Column.
        Set TheCell = Cells(i, cCountry)
        ' Check value of Current Cell against 1st Country.
        If TheCell.Value = Country1 Then
            ' Create a reference to 1st Price Cell Range from Current Cell.
            Set Price1Cell = TheCell
            ' Write the value of the cell at the intersection of current row
            ' and Price Column to 1st Price.
            Price1 = Cells(TheCell.Row, cPrice).Value
            ' A match was found so stop looping.
            Exit For
        End If
    Next

    ' Loop through cells (rows) of Country Column.
    For i = cFR To cLR
        ' Create a reference to Current Cell in Country Column.
        Set TheCell = Cells(i, cCountry)
        ' Check value of Current Cell against 2nd Country.
        If TheCell = Country2 Then
            ' Create a reference to 2nd Price Cell Range from Current Cell.
            Set Price2Cell = TheCell
            ' Write the value of the cell at the intersection of current row
            ' and Price Column to 2nd Price.
            Price2 = Cells(TheCell.Row, cPrice).Value
            ' A match was found so stop looping.
            Exit For
        End If
    Next

    ' Here's the final point. If Country1's price is greater than Country2's
    ' Price, then Country1 should be colored red and Country2 green.
    ' And vice-versa.

    ' Compare Prices
    Select Case Price1
        Case Is > Price2
            Price1Cell.Font.Color = vbRed
            Price2Cell.Font.Color = vbGreen
        Case Is < Price2
            Price1Cell.Font.Color = vbGreen
            Price2Cell.Font.Color = vbRed
    End Select

End Sub

Answer 3

Your code is ordered and substantially correct, except for the object initialization. When you're dealing with objects, you must use Set to initialize them, like this:

Set TheCell = Cells(Counter, 1)

So the final working code should look like this:

Sub CountryComparison()

    Dim Counter As Integer
    Dim Country1 As String
    Dim Country2 As String
    Dim TheCell As Range
    Dim Price1Cell As Range
    Dim Price2Cell As Range
    Dim Price1 As Single
    Dim Price2 As Single

    'The user inputs what countries they want to compare

    Country1 = InputBox("Enter Country 1")
    Country2 = InputBox("Enter Country 2")

    'We are starting at row 2, column 1. Since we're going to check every row, I'm making counter a variable so that I can continuously add 1 to it after every loop.

    Counter = 2
    Set TheCell = Cells(Counter, 1)

    'Here's my loop. It will select TheCell, and if it contains the name of Country1, then it will save that cell as Price1Cell (to be used later), and save the price of a Big Mac in that country (also to be used later). It does the same thing for Country2. And if neither is a match, it goes on to the next row. Since there are 42 rows, it does this until Counter is greater than 43 (maybe it should be until greater than 42, but that shouldn't matter.)

    Do
        TheCell.Select
        If ActiveCell.Value = Country1 Then
        Set Price1Cell = Range(ActiveCell.Address)
        Price1 = ActiveCell.Offset(0, 3).Value
        End If

        If ActiveCell.Value = Country2 Then
        Set Price2Cell = Range(ActiveCell.Address)
        Price2 = ActiveCell.Offset(0, 3).Value
        End If

        Counter = Counter + 1

    Loop Until Counter > 43

    'Here's the final point. If Country1's price is greater than Country2's Price, then Country1 should be colored red and Country2 green. And vice-versa.

    If Price1 > Price2 Then
        Price1Cell.Font.Color = vbRed
        Price2Cell.Font.Color = vbGreen
    End If

    If Price2 > Price1 Then
        Price1Cell.Font.Color = vbGreen
        Price2Cell.Font.Color = vbRed
    End If


End Sub

I did some tests and it works.