Highlighting row containing specific word in specific column
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?
[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
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.
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