Highlight Certain cells in the active row

[Baz_Man]

Hello all, just wondering if this is possible. I have a sheet with sites in Column A. Column B to F are various criteria with Yes or No answers. I'd like to be able to select a site in Column A (Cell A4 for example) and then have every 'Yes' in that route row highlight In a colour to make it stand out. When a different site is selected in a different row I'd like the cross containing Yes on that row to then highligh.

I've searched and can only find a solution that highlights the entire active row rather than just certain cells in it.

Any help would be appreciated.
Cheers

 

[Eric W]

You'd need a macro to do that. Open a copy of your workbook. Right click on the sheet tab on the bottom and select View Code. In the window that opens, paste this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Range("B2:F8").Interior.Color = xlNone
    If Intersect(Range("A2:A8"), Target) Is Nothing Then Exit Sub
    For c = 2 To 6
        If Cells(Target.Row, c) = "Yes" Then Cells(Target.Row, c).Interior.Color = vbRed
    Next c
   
End Sub

Change the ranges in red to what your sheet it. Change the vbRed to the color you want, out of these:

vbRed
vbGreen
vbYellow
vbBlue
vbMagenta
vbCyan
vbWhite

If you want a different color, we can do it a different way. Press Alt-Q to close the editor. This is how my test sheet looks when I select A6:

Book4
	A	B	C	D	E	F
1	Site	Q1	Q2	Q3	Q4	Q5
2	Pyramids	No	Yes	No	No	Yes
3	Great Wall	Yes	No	No	Yes	No
4	Eiffel Tower	No	No	No	No	Yes
5	Statue of Liberty	No	Yes	No	Yes	No
6	Angor Wat	Yes	No	Yes	No	No
7	Uluru	Yes	Yes	No	No	No
8	Kilamanjaro	No	No	No	No	Yes
Sheet9

 

[Baz_Man]

This works a treat, thanks very much!