Highlight other cells when I select another

[edge37]

Hello there!

Spreadsheet example:

[TABLE="width: 858"]
<colgroup><col><col span="11"></colgroup><tbody>[TR]
[TD]Mario Speedwagon.[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Petey Cruiser.[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[/TR]
</tbody>[/TABLE]

I've been wondering if it's possible to do this: When I click on a name, the other cells in the same row that I could be able to specify (with the number values in the example) highlight as well (highlighting preferences would be able to be edited to my likes).

Thanks in advance

 

[NARAYANK991]

Hi ,

If you want merely by clicking the row should be highlighted , then VBA will be required.

If you are willing to use the F9 key to force a recalculate , then a formula based solution is possible.

In any unused worksheet cell , say G2 , enter the formula :

=CELL("address")

Now , let us assume that your CF range would be $E$6:$Q$22 ; select this range , and enter the following CF formula :

=IF(AND($G$2 = "$A$" & ROW(A6), ROW(INDIRECT($G$2)) = ROW()), 1, 0)

Apply any fill colour of your choice.

Now when ever you place the cursor in any cell in the range A6:A22 , and press the F9 key , the corresponding cells in the range E6:Q22 will be highlighted.

 

[edge37]

Thanks for you fast reply. I have 3 questions: 1) Where should I put the second formula? (I got lost when you said "select this range , and enter the following CF formula :"), 2) Do I have to select the range before entering the formula? (dont know how), and, 3) Where do you specify the range to be highlighted in that formula?

Thanks again and sorry for my poor Excel knowledge

 

[NARAYANK991]

Hi ,

In the data that you have posted :

[TABLE="class: cms_table, width: 858"]
<tbody>[TR]
[TD]Mario Speedwagon.[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Petey Cruiser.[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]38[/TD]
[/TR]
</tbody>[/TABLE]

I assume that the names are in column A , starting from row 6. I assume that the numbers are in the range E6:L6 and downwards , let us say till row 22.

You would therefore select the range E6:L22 first.

Now , click on Conditional Formatting , New Rule , Use a formula to determine which cells to format ; the screen will now display a box labelled :

Format values where this formula is true

Enter the formula in this box , and click OK.

 

[edge37]

Cool! it's working. You're the man. Big thanks, NARAYANK991