CODEWITHSUNDEEP
vbaexcelfill
Gerardo Lopez
Gerardo Lopez

Reputation: 51

Search row for cell color and color a given range if condition is true

I have a code in which if the cells in a given range have the word "Yes" they highlight in red. Since the range is really big I also want to shade in red columns A to I if any cell in the same row is filled in red. Here I leave my code.

Sub ChangeColor()
Set MR = Range("A2:CC127")
For Each cell In MR
If cell.Value = "Yes" Then
cell.Interior.ColorIndex = 3
ElseIf cell.Value = "No" Then
cell.Interior.ColorIndex = 15
End If
Next
End Sub

Upvotes: 0

Views: 2031

Answers (3)

DisplayName
DisplayName

Reputation: 13386

you could process only relevant cells

Sub ChangeColor()
    Dim f As Range
    Dim firstAddress As String

    With Range("A2:CC127") ' reference your range
        Set f = .Find(what:="yes", lookat:=xlWhole, LookIn:=xlValues, MatchCase:=False) ' try and find first cell whose content is "yes"
        If Not f Is Nothing Then ' if  found
            firstAddress = f.Address ' store first found cell address
            Do
                f.Interior.ColorIndex = 3 'color found cell 
                Range("A:I").Rows(f.Row).Interior.ColorIndex = 3 ' color columns A to I cells of the same row of found cell    
                Set f = .FindNext(f) ' try and find next "yes"
            Loop While f.Address <> firstAddress ' stop at wrapping back to first found value
        End If
    End With
End Sub

Upvotes: 0

Franz
Franz

Reputation: 360

The following code also colors the entire column of the input range in light red (and all others in light green) as you mentioned:

Const RNG As String = "B1:L6"

Sub ChangeColor()
    Range(RNG).Interior.Color = RGB(191, 255, 191)
    For Each col In Range(RNG).Columns
        alreadycolored = False
        For Each cel In col.Cells
            If InStr(1, cel.Text, "yes", vbTextCompare) > 0 Then 'found
                If Not alreadycolored Then
                    col.Interior.Color = RGB(255, 191, 191)
                    alreadycolored = True
                End If
                cel.Interior.Color = RGB(127, 0, 0)
            End If
        Next cel
    Next col
End Sub

Please feel free to ask if it is unclear why/how it works.

Upvotes: 0

Pierre44
Pierre44

Reputation: 1741

You simply add a line to color also the corresponding cell in A when coloring your cell

Sub ChangeColor()
    Set MR = Range("A2:CC127")
    For Each cell In MR
    If cell.Value = "Yes" Then
    cell.Interior.ColorIndex = 3
    cells(cell.row,1).Interior.ColorIndex = 3   ' NEW LINE HERE
    ElseIf cell.Value = "No" Then
    cell.Interior.ColorIndex = 15
    End If
    Next
End Sub

Upvotes: 1

Related Questions