Attempting to Highlight Cells Based on String within Cell

[rmdlp]

Hello!

Basically what I'm trying to do is the following:

First, determine if I'm in the correct row of cells based on the String value of the cell.
i.e If the current cell's string value contains the string AB1 or AB2, go through the entire row.

Once that has been determined, I would like to highlight the cells either green (if the cell holds a value greater than 5) or blue (if the cell holds a value between 4 and 5).

The above if block is not giving me trouble, it's the initial procedure.

What is stopping me from completing this is the run-time [error '91']: "Object variable or With block variable not set".

Any help would be greatly appreciated.

Sub ChangeCellColor()



Dim columnD As Range
Dim str1, str2 As String
Dim currCell As Range
Dim rightCell As Range
Dim i As Long


str1 = "AB1"
str2 = "AB2"


Columns(1).Font.Color = vbBlack


For i = 1 To Rows.Count


'If the current cell in the D column contains either the string AB1 or AB2, it will look into the values here.
If (currCell.Cells(i, 4).Value = str1) Or (currCell.Cells(i, 4).Value = str2) Then
    'From the cell range of
    For j = 1 To Range("E10").End(xlToRight)
            If rightCell.Cells(j, 5) >= 5# Then
                rightCell.Interior.Color = vbRed
            ElseIf (rightCell.Cells(j, 5) >= 4 And rightCell.Cells(j, 5) <= 4.99) Then
                cell.Interior.Color = vbYellow
            End If
    Next j
    
End If
Next i




End Sub

 

[StephenCrump]

The error message, and the immediate problem, is here:

currCell.Cells(i, 4).Value

because although you have declared currCell as a range, you haven't specified it. (And similar for rightCell).

If you're happy working in the ActiveSheet, Cells(i, 4).Value would suffice.

Other issues (there may be more) ....

1. For i = 1 To Rows.Count 'Suggest you limit to the used portion of Column D

2. For j = 1 To Range("E10").End(xlToRight).Column

3. Do you need to take off,as well as apply, red and yellow colouring, i.e if you no longer have a string match in Column D?

And finally, have you thought about using conditional formatting instead, applied either within Excel, or dynamically using VBA?