Click a cell and all related cells are colour highlighted

[Cat Woman]

In a table I would like to click on a text item in first column and all text
cells that are related to this item would appear highligted in, say, yellow.
Procedure able to be repeated for any item in first column.

I am Excel beginner so if anyone knows how to do this, step by step
instructions would be great. Thank you.

 

[Jacob Skaria]

Dear

If you are new to VBA set the Security level to low/medium in
(Tools|Macro|Security). Right click on the sheet tab on which you would like
to have this highligtion and click on 'View Code'. Paste the below code.

I have defined the applicable range as B1:J10 and the range in Col A as
A1:A10. You can change this as per your requirement..I hope you understand
the code which is rather simple. Setting the color to xlNone. With a loop it
checks whether any of the cells contain a value same as that of target. If
found color the interior. If you want to highlight blanks uncomment the two
marked lines (which appears in green within VBE).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngTemp As Range
Set rngTemp = Range("B1:J10")
rngTemp.Interior.ColorIndex = xlNone
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
'If Target.Value <> "" Then
For Each cell In rngTemp
If Target.Value = cell.Value Then
cell.Interior.Color = vbYellow
End If
Next
'End If
End If
End Sub

Try and feedback. Wish you all good luck..

If this post helps click Yes

 

[Gord Dibben]

Who or what determines which cells are "related" to the clicked cell?


Gord Dibben MS Excel MVP

On Fri, 29 May 2009 17:32:06 -0700, Cat Woman <Cat

 

[Jacob Skaria]

Dear "Cat Woman"

Seeing Gord's response I realise..I have earlier assumed that your query was
to highlight all cells in which ever row they are...(within the range).For
example if Cell A2 = "cat" and if you select cell A2 the macro will highlight
all cells that contain "cat" within the range "B1:J10"..

OR

If you mean to highlight the entire row..then use the below..

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
Rows(Target.Row).Interior.Color = vbYellow
End Sub

If this post helps click Yes

 

[Gord Dibben]

One more question from Gord.

Would you want the highlighted related cells to remain highlighted after you
click on another cell?


Gord

 

[Shane Devenshire]

Translate the word "related".

If cell A1 contains cat and D1 contains dog are they related because they
each contain 3 letters, or because they are both animals, or because they are
both domestic pets? In cell A1 contains 1357 and D1 contains 995 are these
two cells related because the third digit is 5 in both cases or because both
entries are numbers or because both are less than 1500 or because both are
greater than 0 or because they are each whole numbers, or................