Change cell color if row contains color

Zappd · Jun 1st 2017

Support,

Want columns A and B to be highlighted the same color if E through V contains that color.
Only colors that matter are yellow ColorIndex = 3 and red ColorIndex = 6.
If the row contains both colors red will be displayed.
If the row does not contain red or yellow then no change will be made to the A or B column.

To better explain:
In the attached spreadsheet A5 and B5 would be yellow because E5 is yellow.
In the attached spreadsheet A14 and B14 would be red because I14 is red.

Tried a few different things from the site here but was unable to find anything that I could get to work.
Tried using CF but could not figure out how to match ColorIndex.

Have no issue using CF if that is solution.

There is a Workbook_SheetSelectionChange that captures the color change and counts the cells.
Check Compliance button is redundant and will probably be removed.
There are is a dynamic range aRange that contains E5-V32.

If anything is unclear or there are any questions please let me know.

Micah

skywriter · Jun 2nd 2017

Re: Change cell color if row contains color

Hi Zappd,

You can try this code.

Be advised I have written almost no code for months so I'm very rusty, there's probably a better way to do this.

It works off of the worksheet change event, you could put it in a regular module.

It's lightly tested and there's no error handling.

Good Luck :cheers:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("aRange")) Is Nothing Then Exit Sub
Dim rCount As Long, yCount As Long, r As Long, c As Long
Application.ScreenUpdating = False
With Range("aRange")
    For r = 1 To .Rows.Count
        For c = 1 To .Columns.Count
                If .Cells(r, c).Interior.Color = vbYellow Then yCount = yCount + 1
                If .Cells(r, c).Interior.Color = vbRed Then
                    .Cells(r, -3).Interior.Color = vbRed: .Cells(r, -2).Interior.Color = vbRed
                    rCount = 1
                    Exit For
                End If
        Next c
            If yCount >= 1 And rCount = 0 Then .Cells(r, -3).Interior.Color = vbYellow: .Cells(r, -2).Interior.Color = vbYellow
            If yCount = 0 And rCount = 0 Then .Cells(r, -3).Interior.Color = vbWhite: .Cells(r, -2).Interior.Color = vbWhite
            rCount = 0: yCount = 0
    Next r
End With
Application.ScreenUpdating = True
End Sub

Display More

Zappd · Jun 2nd 2017

Re: Change cell color if row contains color

Bruce,

Added to the Workbook_SheetSelectionChange event and it appears to working.

Thank you for the quick response.
Micah

skywriter · Jun 2nd 2017

Re: Change cell color if row contains color

My pleasure, thanks for the feedback. :cool:

Change cell color if row contains color

Participate now!

Share