Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Click a cell and all related cells are colour highlighted
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Click a cell and all related cells are colour highlighted
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 --------------- Jacob Skaria "Cat Woman" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Click a cell and all related cells are colour highlighted
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 wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Click a cell and all related cells are colour highlighted
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 --------------- Jacob Skaria "Cat Woman" wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Click a cell and all related cells are colour highlighted
One more question from Gord.
Would you want the highlighted related cells to remain highlighted after you click on another cell? Gord On Sat, 30 May 2009 10:37:01 -0700, Jacob Skaria wrote: 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 --------------- Jacob Skaria "Cat Woman" wrote: 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Click a cell and all related cells are colour highlighted
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................ -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Cat Woman" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I change the colour of highlighted cells - too faint | Setting up and Configuration of Excel | |||
Click one cell sheet1, display all related recs on sheet2 | Excel Worksheet Functions | |||
click on one cell to find and highlight a related cell? | Excel Discussion (Misc queries) | |||
Colour of highlighted cells | Excel Discussion (Misc queries) | |||
left click on a cell and more cells are highlighted | Excel Worksheet Functions |