magic cell

[omega]

Hi, I’m a newbie. I encountered a code in this site and I tweaked it to
attain my goal but there is a limitation to this code and I want to modify it
further.

This is written in Sheet1:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = Range("H4").Address Then
DisplayFarCell_AA
End If
If Target.Address = Range("I4").Address Then
DisplayFarCell_AB
End If
End Sub

This is written in Module1:
Sub DisplayFarCell_AA()
Range("AA4").Select
Range("H4").Select
End Sub
Sub DisplayFarCell_AB()
Range("AB4").Select
Range("I4").Select
End Sub
The sheet has its “freeze pane†at cell O7. When you select cell H4, cell
AA4 will automatically display at the right side of the screen, then the
cursor goes back to H4.
As you know, this is only limited to cell H4. My goal is to watch an active
cell, column per column. Instead of just watching “H4†to be selected, I want
to watch H4 to H555 and I4 to I555 and so on.
Quick explanation of the goal:
Watch columns, ranges H4:H555, I4:I555, J4:J555, K4:H555
If one cell in range H4:H555 is selected,
then display AA4 (column AA),
then go back to selected cell.
If one cell in range I4:I555 is selected,
then display AB4 (column AB),
then go back to selected cell.
If one cell in range J4:J555 is selected,
then display AC4 (column AC),
then go back to selected cell.
If one cell in range K4:H555 is selected,
then display AD4 (column AD),
then go back to selected cell.

Note: Instead of displaying a cell, I may want to display a chart. It would
then be like this:
If one cell in range H4:H555 is selected,
then display CHART1
then go back to selected cell.
If another cell is but still in range H4:H555
then still display CHART1
then go back to selected cell.
If another cell is but in another range -- I4:I555
then hide CHART1 then display CHART2
then go back to selected cell.

Thank you so much for your help. I am using excel 2007.

 

[Nigel]

To monitor a column use something like

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 8 Then DisplayFarCell_AA
End Sub



--

Regards,
Nigel
(e-mail address removed)

 

[omega]

Nigel,

It works! Thank you very much for your help!

Omega

 

[Don Guillett]

I can't quite figure out what you want but if you select row 4 in any column
it will select that column offset 21 from row 4 to row 555. So, selecting
h4 will select ac4:ac555. If you want this to happen from any cell in col H,
just comment out or delete the if row line

Private Sub Worksheet_SelectionChange _
(ByVal Target As Range)
If Target.Row <> 4 Then Exit Sub
Application.EnableEvents = False
Cells(Target.Row, Target.Column). _
Offset(, 21).Resize(552).Select

MsgBox "Click OK to go back to " & Target.Address
Target.Select

Application.EnableEvents = True
End Sub

 

[omega]

Don Guillett,

Wow! As per your advice, I edit out some lines and with some trial and
error, I have reached the desired result. Amazingly, just five lines of the
code you gave replaced my bulky codes before.

Below is the edited version of the code that works perfectly for my project:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
Cells(Target.Row, Target.Column).Offset(, 26).Select
Target.Select
Application.EnableEvents = True
End Sub

Thank you very much!

Omega

======================================================

 

[Don Guillett]

Glad to help. I like to keep it simple

 

[Rick Rothstein]

What is it you think these 5 lines of code (actually, I only see 4 lines of
code) you posted are doing??? All I see them doing is re-selecting the cell
you just selected.

 

[Don Guillett]

Rick, I couldn't figure it out either. That's why I posted the way I did
Private Sub Worksheet_SelectionChange _
(ByVal Target As Range)
If Target.Row <> 4 Then Exit Sub
Application.EnableEvents = False
Cells(Target.Row, Target.Column). _
Offset(, 21).Resize(552).Select

MsgBox "Click OK to go back to " & Target.Address
Target.Select

Application.EnableEvents = True
End Sub

 

[omega]

Rick,

It’s okay. The edited version of Don’s code works well in my worksheet
because it has a “freeze pane†at AE6. My goal is to display a group of
charts contained in the right side of the pane, column AE, when a cell in
column E (left side of the pane) is selected. The chart serves as a visual
guide to the user depending on the cell/column selected. Without the “freeze
paneâ€, the code will not achieve anything. Furthermore, when the user selects
a cell in column F, at the left side of the pane, the code automatically
scrolls the right side of the pane and displays column AF where another group
of charts is present, then goes back to the previously selected cell at the
left side of the “freeze paneâ€â€¦. And so on…

This primitive way of displaying a guide chart for the user works fine with
me as of the meantime, until I will learn a way to pop-up a “group of chartsâ€
when a cell in a column is selected.

By the way, is this possible? …popping up a group of charts when a cell is
selected?

Thank you,

Omega

===============================

 

[Don Guillett]

ActiveSheet.Shapes("Chart 2").Visible = False