CODEWITHSUNDEEP
excelvba
Kari
Kari

Reputation: 11

VBA Change row color based on cell value

I am trying to automate a massive report and one step of the process involves changing the row color based on the value in column B.

Essentially, if B# = "SCC NUPSFTPDE", then I need the row color to be a light blue. (I'm not overly concerned with the exact color TBH).

I've been trying to manipulate code and have basically made my own Frankenstein code so I'm sure it's wrong somewhere in here. Please help! 

Dim LastRow As Long
Dim cell As Range
sSheetName = ActiveSheet.Name
With Worksheets(sSheetName)
    LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
    **For Each cell In Range("B2:B" & LastRow)
    If cell.Value = "SCC NUPSFTPDE" Then
        ColorRow = 39**
    Else
        cell.EntireRow.Interior.ColorIndex = xlNone
    End If
Next
End With

Upvotes: 0

Views: 4311

Answers (2)

Error 1004
Error 1004

Reputation: 8220

You could also try worksheet event - Worksheet_Change which apply the color in every change automatically.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    Dim LastRow As Long

    With Me

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

        If Not Intersect(Target, .Range("B2:B" & LastRow)) Is Nothing Then

            For Each cell In Target

                Application.EnableEvents = False

                    If cell.Value = "SCC NUPSFTPDE" Then
                        cell.EntireRow.Interior.ColorIndex = 39
                    Else
                        cell.EntireRow.Interior.ColorIndex = xlNone
                    End If

                Application.EnableEvents = True

            Next cell

        End If

    End With

End Sub

Upvotes: 0

BigBen
BigBen

Reputation: 49998

Just to close this question out: change

ColorRow = 39

to

cell.EntireRow.Interior.ColorIndex = 39

or perhaps better, something like

cell.EntireRow.Interior.Color = RGB(129, 218, 239)

Upvotes: 1

Related Questions