CODEWITHSUNDEEP
excelvbacellhighlightconditional-formatting
geravie498
geravie498

Reputation: 3

How to highlight all cells of the same value in a column when a specific text is in another column

Hi I'm trying to use either VBA or conditional formatting for this but it doesn't work the way I want it to :/

Column B is a list of values that are keyed in by different people one at a time, column C is the status of the person. What I'm trying to achieve: ONLY when column C is "OUT", the value in the adjacent cell (in column B) is shaded and all the same values of that cell is shaded as well.

I can shade the column B cell given "OUT" in column C but I can't get all the same values before that to be shaded as well. There are 3 possible status: NEW, AFTERNOON, OUT

Anyone have any ideas please? I attached a photo I hope it explains abit clearer

enter image description here

Upvotes: 0

Views: 991

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149335

Is there a way to highlight more than 2 cells? If I have 3 of same value, only the last 2 duplicates will be highlighted – geravie498 5 hours ago

In such a case you only need one rule.

Let's assume the data is in B1:C10. Adapt the formula accordingly.

Match all the value of B1 in the range below where $B$1:$B$10=$B1 and $C$1:$C$10="OUT"

RULE

=INDEX($B$1:$B$10,MATCH(1,($B$1:$B$10=$B1)*($C$1:$C$10="OUT"),0))

enter image description here

Upvotes: 1

Super Symmetry
Super Symmetry

Reputation: 2875

I really like @Siddharth's but for completeness here's a way you can do it in VBA: Paste this code into your sheet's module

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lCodesCol As Long: lCodesCol = 3
    If Target.Column <> lCodesCol And Target.Column <> lCodesCol - 1 Then Exit Sub
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Dim vData As Variant
    Dim i As Long, j As Long
    Dim lFirstRow As Long: lFirstRow = 2
    Dim lLastRow As Long
    Dim rngToHighlight As Range
    
    With Me
        lLastRow = WorksheetFunction.Max(lFirstRow, _
                                         .Cells(.Rows.Count, lCodesCol).End(xlUp).Row, _
                                         .Cells(.Rows.Count, lCodesCol - 1).End(xlUp).Row)
        vData = .Range(.Cells(1, lCodesCol - 1), .Cells(lLastRow, lCodesCol)).Value
        
        For i = lFirstRow To lLastRow
            If vData(i, 2) = "OUT" And vData(i, 1) <> "" Then
                For j = lFirstRow To lLastRow
                    If vData(i, 1) = vData(j, 1) Then
                        If rngToHighlight Is Nothing Then
                            Set rngToHighlight = .Cells(j, lCodesCol - 1)
                        Else
                            Set rngToHighlight = Union(.Cells(j, lCodesCol - 1), rngToHighlight)
                        End If
                    End If
                Next j
            End If
        Next i
        
        With .Cells(lFirstRow, lCodesCol - 1).Resize(lLastRow, 1).Interior
            .Pattern = xlNone
        End With
        
        If Not rngToHighlight Is Nothing Then
            With rngToHighlight.Interior
                .Pattern = xlSolid
                .Color = RGB(200, 200, 200)
            End With
        End If
    End With
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

Please note that conditional formatting is much faster than any vba code you can write and the differences get more significat as your data grows.

Upvotes: 0

Related Questions