Highlighting row containing specific word in specific column

[xopheus]

Hi!

I am working with a project where we are using a simple excel file with data on our personell.
I would like to create a function in the spreadsheet that highlights certain rows based on what groups the person is part of.
I've made a small example file representing the data.

I would like to be able to select cell E2 and that would highlight cells A2:C2 and A6:C6 because both those people are in the group "economy".
If selecting the cell E2 is not possible as a trigger, then a macro button in G2 would suffice.

Is there a way to create this functionality and can someone guide me in how to do this?

Most pleased with any help you can offer!

Best regards,
Andreas

[offthelip]

try this:

[vba]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String


If Not (Intersect(Target, Range("E2:E5")) Is Nothing) Then
str = Target.Value
With Range("C1:C7")
.ClearFormats
.FormatConditions.Add Type:=xlTextString, String:=str, _
TextOperator:=xlContains
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
.FormatConditions(1).StopIfTrue = False
End With
End If


End Sub


[/VBA]

[xopheus]

Hi again!
I really appreciate your fast reply! Wow!

In the real file we use, there are lots more collumns and of course rows too.
Is there a way to make the highlight for all cells for that person?
In the example it would be cells A2, B2 and C2 instead of only C2 in your example.

Many thanks!

[offthelip]

I had to do it a different way not using conditional formatting but this should work, note I have only done it for one additional column

[vba]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim colr As Range
Dim Ncell As Range
Dim str As String
If Selection.Cells.Count = 1 Then
If Not (Intersect(Target, Range("E2:E5")) Is Nothing) Then
str = Target.Value
Set colr = Range("a2:c7")


For Each Ncell In colr
With Ncell
place = InStr(Ncell.Value, str)
If place > 0 Then
.Interior.Pattern = xlSolid
.Interior.PatternColorIndex = xlAutomatic
.Interior.ThemeColor = xlThemeColorAccent2
.Interior.TintAndShade = 0.399975585192419
.Interior.PatternTintAndShade = 0

Ncell.Offset(o, -1).Interior.Pattern = xlSolid
Ncell.Offset(o, -1).Interior.PatternColorIndex = xlAutomatic
Ncell.Offset(o, -1).Interior.ThemeColor = xlThemeColorAccent2
Ncell.Offset(o, -1).Interior.TintAndShade = 0.399975585192419
Ncell.Offset(o, -1).Interior.PatternTintAndShade = 0


Else
.Interior.Pattern = xlNone
.Interior.TintAndShade = 0
.Interior.PatternTintAndShade = 0
End If
End With
Next Ncell
End If
End If
End Sub




[/vba]