Formatting a specific cell based on another cell's content

Question

I found code and tried to incorporate it.

I get

run-time error '9' subscript out of range

When I meet my criteria if cell b1 = "TOTAL" then I want to look at corresponding cell c1 and determine if it is less than 8000.
If it is, I want to highlight it green.
If it is more than 8000 I want to highlight it red.
If b1 is anything other than "TOTAL" I want to go to the next row and do nothing to c1.

Data:

Dim i As Long
Dim LastRow As Long
Dim wS As Worksheet

Set wS = ThisWorkbook.Sheets("Gateway")
LastRow = LastRow_1(wS)
For i = 1 To LastRow
    With wS
        If .Cells(i, 2) = "TOTAL" And .Cells(i, 3) < 8000 Then
            .Cells(i, 3).FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
            With Selection.FormatConditions(1).Font
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = 0
            End With
            With .Cells(i, 3).FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .Color = 255
                .TintAndShade = 0
            End With
            Selection.FormatConditions(1).StopIfTrue = False
        Else
            'B and C empty
            '.Cells(i, 3) format red
        End If
    End With 'wS
Next i

End Sub


Public Function LastRow_1(wS As Worksheet) As Double

    With wS
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            LastRow_1 = .Cells.Find(What:="*", _
                                After:=.Range("c1"), _
                                Lookat:=xlPart, _
                                LookIn:=xlFormulas, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlPrevious, _
                                MatchCase:=False).Row
        Else
            LastRow_1 = 1
        End If
    End With
    
End Function

Answer 1

Try the modified code below (comments within the code).

Note - not sure if you meant to run it for a loop for entire column B and C, or just a single cell.

Option Explicit

Sub ColorCellC1()

Dim i As Long, LastRow As Long
Dim wS As Worksheet

Set wS = ThisWorkbook.Sheets("Gateway")
LastRow = FindLastRow(wS)

If LastRow = 0 Then ' if worksheet "Gateway" does not conatin any data
    Exit Sub
End If

For i = 1 To LastRow
    With wS
        If .Range("B" & i).Value = "TOTAL" Then
            If .Range("C" & i).Value < 8000 Then
                .Range("C" & i).Interior.Color = vbGreen
            Else
                .Range("C" & i).Interior.Color = vbRed
            End If
        
        Else
            ' do Nothing
        End If
    End With 'wS
Next i

End Sub


Function FindLastRow(Sht As Worksheet) As Long

' This Function finds the last row in a worksheet, and returns the row number

Dim LastCell As Range

With Sht
    Set LastCell = .Cells.Find(What:="*", After:=.Cells(1), LookAt:=xlPart, LookIn:=xlFormulas, _
                        SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
    If Not LastCell Is Nothing Then
        FindLastRow = LastCell.Row
    Else
        FindLastRow = 0
    End If
End With

End Function