Attempting to Highlight Cells Based on String within Cell

**rmdlp** · 10-23-2014, 01:45 PM

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".

I do have some programming experience, I'm just having trouble with the syntax of VBA. 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

**stnkynts** · 10-23-2014, 01:59 PM

The primary reason you are getting the error is because you didn't set what currCell is.

Enable "Require Variable Declaration" to prevent this in the future.

**rmdlp** · 10-23-2014, 02:08 PM

Hmmm, ok! Thank you for your prompt response, I thought declaring the variable (Dim currCell As Range) would do it.

I have currently set it to:

Set currCell = Range("E10: AI155")

But now I`m receiving a run-time error `1004`: Application-defined or object-defined error on the first if statement. Any suggestions?

**stnkynts** · 10-23-2014, 02:17 PM

Submit an example workbook with before and after representations on what should be highlighted blue and green.

**rmdlp** · 10-23-2014, 02:28 PM

Here are before and after workbooks as requested! Thank you again for your help.

https://www.dropbox.com/s/9jj980pme1...efore.xls?dl=0
https://www.dropbox.com/s/cdnckqa66q...After.xls?dl=0

EDIT: Sorry I have the colors reversed in the workbooks.

**D0se** · 10-23-2014, 02:23 PM

There you go:

Sub ChangeCellColor()



Dim str1, str2 As String
Dim lastrow As Integer
Dim lastcol As Integer

str1 = "AB1"
str2 = "AB2"


Columns(1).Font.Color = vbBlack

lastrow = Cells(1, 4).End(xlDown).Row



For i = 1 To lastrow

'If the current cell in the D column contains either the string AB1 or AB2, it will look into the values here.
If Cells(i, 4).Value = str1 Or Cells(i, 4).Value = str2 Then
lastcol = Cells(i, 4).End(xlToRight).Column
For j = 5 To lastcol
If Cells(i, j) > 5 Then
Cells(i, j).Interior.ColorIndex = 3
ElseIf Cells(i, j) < 6 And Cells(i, j) > 3 Then
Cells(i, j).Interior.ColorIndex = 5
End If
Next j
End If
Next i

End Sub

**rmdlp** · 10-23-2014, 02:40 PM

Oh wow, thank you for your response D0se but unfortunately the code you have provided is not doing what I hoped it would do.

**stnkynts** · 10-23-2014, 04:10 PM

Try this:

Sub MainMacro()
Dim str1 As String, str2 As String
Dim rng As Range, rCell As Range, rCol As Range

str1 = "AB1"
str2 = "AB2"

Set rng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)

For Each rCell In rng
    If rCell = str1 Or rCell = str2 Then
        For Each rCol In rCell.Resize(1, Cells(rCell.Row, Columns.Count).End(xlToLeft).Column + 1)
            If IsNumeric(rCol) Then
                If rCol >= 4 And rCol < 5 Then
                    rCol.Interior.Color = vbRed
                ElseIf rCol >= 5 Then
                    rCol.Interior.Color = vbGreen
                End If
            End If
        Next rCol
    End If
Next rCell

End Sub

**D0se** · 10-23-2014, 04:30 PM

after seeing your sample workbook heres the modded code, but please delete any empty lines on the worksheet.

Sub ChangeCellColor()



Dim str1, str2 As String
Dim lastrow As Integer
Dim lastcol As Integer

str1 = "AB1"
str2 = "AB2"


Columns(1).Font.Color = vbBlack

lastrow = Cells(1, 4).End(xlDown).Row



For i = 1 To lastrow

'If the current cell in the D column contains either the string AB1 or AB2, it will look into the values here.
If Cells(i, 1).Value = str1 Or Cells(i, 1).Value = str2 Then
lastcol = Cells(i, 1).End(xlToRight).Column
For j = 2 To lastcol
If Cells(i, j).Value > 5 Then
Cells(i, j).Interior.ColorIndex = 4
ElseIf Cells(i, j).Value < 6 And Cells(i, j).Value > 3 Then
Cells(i, j).Interior.ColorIndex = 5
End If
Next j
End If
Next i

End Sub

Attempting to Highlight Cells Based on String within Cell

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Attempting to Highlight Cells Based on String within Cell

Re: Attempting to Highlight Cells Based on String within Cell

Re: Attempting to Highlight Cells Based on String within Cell

Re: Attempting to Highlight Cells Based on String within Cell

Re: Attempting to Highlight Cells Based on String within Cell

Re: Attempting to Highlight Cells Based on String within Cell

Re: Attempting to Highlight Cells Based on String within Cell

Re: Attempting to Highlight Cells Based on String within Cell

Re: Attempting to Highlight Cells Based on String within Cell

Thread Information

Users Browsing this Thread

Similar Threads

Attempting to autofilter a dataset based on keywords which are part of a text string

[SOLVED] Count cells in row based on cell value, highlight row if wrong number or cells.

Highlight 1 cell based on value from other cells

[SOLVED] Highlight row based on character/text string containing in cell.

Highlight or colour a cell or cells based on cells in another range.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions