Color the range if multiple conditions satisfied

[Mithunrnair]

Need a help to highlight the respective cells, if a user (name) in rage A have an SIA and WU value in rage B.

The code I have, dosent work completely but some what give an idea where I am now.

Sub HighlightCell ()

Dim cell As Range, rg As Range, rng As Range, cond1 As FormatCondition, cond2 As FormatCondition, cond3 As FormatCondition

Range("A1", Range("A2").End(xlDown)).Select
    Selection.Copy
    Range("F1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

 ActiveSheet.Range("F2").End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlYes

Set rg = Range("B2", Range("B2").End(xlDown))
Set rng = Range("F2", Range("F2").End(xlDown))

For Each cell In rng
rg.FormatConditions.Delete

Set cond1 = rg.FormatConditions.Add(xlCellValue, xlEqual, "SIA")
Set cond2 = rg.FormatConditions.Add(xlCellValue, xlEqual, "WU")

'define the format applied for each conditional format

With cond1
.Interior.Color = vbGreen
.Font.Color = vbWhite
End With

With cond2
.Interior.Color = vbGreen
.Font.Color = vbWhite
End With

Next cell

End Sub

Any help really appreciated.... Thanks in advance

 

[Joe4]

You do not need VBA to do this. It can be done with Conditional Formatting.
Just highlight columns A and B, and enter this Conditional Formatting formula:

=and(countifs($A:$A,$A1,$B:$B,"SIA")>0,countifs($A:$A,$A1,$B:$B,"WU")>0)

and choose your yellow highlighting option.

If you would VBA code to do this, you can turn on the VBA recorder, and record yourself applying this Conditional Formatting.

 

[Norie]

How is the posted code not working?

 

[Mithunrnair]

How is the posted code not working?

Thanks, I dont have deep knowledge in VBA, i am still on learning face.

The code will only highlight all the cells which have vale SIA and WU. But i need it to be highlighted in way that it showed in the image i added here.

 

[Mithunrnair]

You do not need VBA to do this. It can be done with Conditional Formatting.
Just highlight columns A and B, and enter this Conditional Formatting formula:

=and(countifs($A:$A,$A1,$B:$B,"SIA")>0,countifs($A:$A,$A1,$B:$B,"WU")>0)

and choose your yellow highlighting option.

If you would VBA code to do this, you can turn on the VBA recorder, and record yourself applying this Conditional Formatting.

Thsnks, but it will highlight all the cell with values SIA and WU. I need only specific cells to be highlighted as the photo i added here.... for exg if Alex have SIA and WU his name should be highlighted.... if Bob have only SIA leave that record, if next pwrson have only WU leave him. And finally Don have SIA and WU he need to be highlighted.... like this it has to perform for each users in row A

 

[Mithunrnair]

Thanks, but it will highlight all the cell with values SIA and WU. I need only specific cells to be highlighted as the photo i added here.... for exg if Alex have SIA and WU his name should be highlighted.... if Bob have only SIA leave that record, if next pwrson have only WU leave him. And finally Don have SIA and WU he need to be highlighted.... like this it has to perform for each users in row A

Thanks, it will partially solve my issue, because it will mark all of them as true, if it found the SIA and WU for a user.... (image attached) any other better solution expected.....

 

[Joe4]

So, are you saying that you only want to highlight the two records where the changeover in column B happens?
Is your data always sorted like that, by column A first, then by column B, or might the records be mixed?

 

[Mithunrnair]

So, are you saying that you only want to highlight the two records where the changeover in column B happens?
Is your data always sorted like that, by column A first, then by column B, or might the records be mixed?

Yes, It will be always sorted in manner and yes I need to get a combinations of SIA and WU highlighted always for each users, if a user have only one combination it has to be highlighted and if a user have 4 entries 2 SIA and 2 WU then I need all those 4 to be highlighted.... i know it sounds bit complicated.... this is how i do this manually and on top of 3000 records I have to do this analyses everymonth....

 

[Joe4]

if a user have 4 entries 2 SIA and 2 WU then I need all those 4 to be highlighted....

Can you please post what that would look like, as that does not seem to mesh with the statement with how the data is to be sorted?