Is it possible to set the formatting of a cell based on the current position
of the cursor in the worksheet?
For example, I want to highlight cell C3 if the cursor is currently in cell
G10 and then have that highlight go away when the cursor moves to any other
cell.

See if this helps you

http://groups.google.com/group/micro...08c702c28ac544

Alok

"Ted M H" wrote:

Is it possible to set the formatting of a cell based on the current position
of the cursor in the worksheet?
For example, I want to highlight cell C3 if the cursor is currently in cell
G10 and then have that highlight go away when the cursor moves to any other
cell.

Hi Ted,
I sent you a link which may be too long to handle.

Here is the code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Static c As Range
Static ci As Integer
If Not c Is Nothing Then
c.Interior.ColorIndex = ci
End If
ci = Target.Cells(1).Interior.ColorIndex
Target.Cells(1).Interior.ColorIndex = 36
Set c = Target.Cells(1)


End Sub




"Ted M H" wrote:

Is it possible to set the formatting of a cell based on the current position
of the cursor in the worksheet?
For example, I want to highlight cell C3 if the cursor is currently in cell
G10 and then have that highlight go away when the cursor moves to any other
cell.

Yikes! Thanks a bunch for the quick reply, Alok. I probably should have
mentioned in my original post that I'm not a coder...I work at the formula
and superficial macro levels, but code throws me for a loop, so to speak.
Any chance you could help me understand how I would take the code that you
have so generously provided and use it in my spreadsheet? Could I somehow
insert it into the conditional formatting dialog box as one of the
conditions, or is it a lot more complicated than that?

"Alok" wrote:

Hi Ted,
I sent you a link which may be too long to handle.

Here is the code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Static c As Range
Static ci As Integer
If Not c Is Nothing Then
c.Interior.ColorIndex = ci
End If
ci = Target.Cells(1).Interior.ColorIndex
Target.Cells(1).Interior.ColorIndex = 36
Set c = Target.Cells(1)


End Sub




"Ted M H" wrote:

Is it possible to set the formatting of a cell based on the current position
of the cursor in the worksheet?
For example, I want to highlight cell C3 if the cursor is currently in cell
G10 and then have that highlight go away when the cursor moves to any other
cell.

Hi Ted,

It is just a little more complicated than that.

First off I gave you the code because in my view there is no Conditional
formatting that can do what you are asking for.

I will try and explain what you need to do
Right Click on the tab of the sheet on which you want to put this feature
Paste the code in the window that you see. (Just to clarify the window will
have two drop down boxes - one reading (Declarations) and the other
(General). There may alreay be a line of text reading "Option Explicit". If
there is such a line you will paste the code I sent you below that line (one
line below or 10 lines below, does not matter) otherwise you will paste it
anywhere in that window.

That is all there is to it.

Alok



"Ted M H" wrote:

Yikes! Thanks a bunch for the quick reply, Alok. I probably should have
mentioned in my original post that I'm not a coder...I work at the formula
and superficial macro levels, but code throws me for a loop, so to speak.
Any chance you could help me understand how I would take the code that you
have so generously provided and use it in my spreadsheet? Could I somehow
insert it into the conditional formatting dialog box as one of the
conditions, or is it a lot more complicated than that?

"Alok" wrote:

Hi Ted,
I sent you a link which may be too long to handle.

Here is the code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Static c As Range
Static ci As Integer
If Not c Is Nothing Then
c.Interior.ColorIndex = ci
End If
ci = Target.Cells(1).Interior.ColorIndex
Target.Cells(1).Interior.ColorIndex = 36
Set c = Target.Cells(1)


End Sub




"Ted M H" wrote:

Is it possible to set the formatting of a cell based on the current position
of the cursor in the worksheet?
For example, I want to highlight cell C3 if the cursor is currently in cell
G10 and then have that highlight go away when the cursor moves to any other
cell.

Hi Alok,

Thanks for your patience. I plugged the code in as you instructed and it's
pretty cool. However, it is highlighting whatever cell the cursor is placed
in rather than the cell I want it to highlight. In my simple example, here's
what I'm trying to do:
If the cursor is in cell G10, I want cell C3 to be highlighted. If the
cursor is in any cell other than G10 I do not want cell C3 to be highlighted.
My actual application will be more complicated than this, but I figure if I
can get something that will do this simple function, then I can work with it
on a trial and error basis to get it to do the more complex functions
(Although I'm not a coder I am able to edit macros, albeit not very
efficiently).

Ted

"Alok" wrote:

Hi Ted,

It is just a little more complicated than that.

First off I gave you the code because in my view there is no Conditional
formatting that can do what you are asking for.

I will try and explain what you need to do
Right Click on the tab of the sheet on which you want to put this feature
Paste the code in the window that you see. (Just to clarify the window will
have two drop down boxes - one reading (Declarations) and the other
(General). There may alreay be a line of text reading "Option Explicit". If
there is such a line you will paste the code I sent you below that line (one
line below or 10 lines below, does not matter) otherwise you will paste it
anywhere in that window.

That is all there is to it.

Alok



"Ted M H" wrote:

Yikes! Thanks a bunch for the quick reply, Alok. I probably should have
mentioned in my original post that I'm not a coder...I work at the formula
and superficial macro levels, but code throws me for a loop, so to speak.
Any chance you could help me understand how I would take the code that you
have so generously provided and use it in my spreadsheet? Could I somehow
insert it into the conditional formatting dialog box as one of the
conditions, or is it a lot more complicated than that?

"Alok" wrote:

Hi Ted,
I sent you a link which may be too long to handle.

Here is the code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Static c As Range
Static ci As Integer
If Not c Is Nothing Then
c.Interior.ColorIndex = ci
End If
ci = Target.Cells(1).Interior.ColorIndex
Target.Cells(1).Interior.ColorIndex = 36
Set c = Target.Cells(1)


End Sub




"Ted M H" wrote:

Is it possible to set the formatting of a cell based on the current position
of the cursor in the worksheet?
For example, I want to highlight cell C3 if the cursor is currently in cell
G10 and then have that highlight go away when the cursor moves to any other
cell.

Hi Ted
I understand now.
Go back into the code of the sheet like I have explained. Delete what you
have there and replace with the following

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells(1).Address = "$G$10" Then
Range("$C$3").Interior.ColorIndex = 36
Else
Range("$C$3").Interior.ColorIndex = xlNone
End If
End Sub

Alok

"Ted M H" wrote:

Hi Alok,

Thanks for your patience. I plugged the code in as you instructed and it's
pretty cool. However, it is highlighting whatever cell the cursor is placed
in rather than the cell I want it to highlight. In my simple example, here's
what I'm trying to do:
If the cursor is in cell G10, I want cell C3 to be highlighted. If the
cursor is in any cell other than G10 I do not want cell C3 to be highlighted.
My actual application will be more complicated than this, but I figure if I
can get something that will do this simple function, then I can work with it
on a trial and error basis to get it to do the more complex functions
(Although I'm not a coder I am able to edit macros, albeit not very
efficiently).

Ted

"Alok" wrote:

Hi Ted,

It is just a little more complicated than that.

First off I gave you the code because in my view there is no Conditional
formatting that can do what you are asking for.

I will try and explain what you need to do
Right Click on the tab of the sheet on which you want to put this feature
Paste the code in the window that you see. (Just to clarify the window will
have two drop down boxes - one reading (Declarations) and the other
(General). There may alreay be a line of text reading "Option Explicit". If
there is such a line you will paste the code I sent you below that line (one
line below or 10 lines below, does not matter) otherwise you will paste it
anywhere in that window.

That is all there is to it.

Alok



"Ted M H" wrote:

Yikes! Thanks a bunch for the quick reply, Alok. I probably should have
mentioned in my original post that I'm not a coder...I work at the formula
and superficial macro levels, but code throws me for a loop, so to speak.
Any chance you could help me understand how I would take the code that you
have so generously provided and use it in my spreadsheet? Could I somehow
insert it into the conditional formatting dialog box as one of the
conditions, or is it a lot more complicated than that?

"Alok" wrote:

Hi Ted,
I sent you a link which may be too long to handle.

Here is the code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Static c As Range
Static ci As Integer
If Not c Is Nothing Then
c.Interior.ColorIndex = ci
End If
ci = Target.Cells(1).Interior.ColorIndex
Target.Cells(1).Interior.ColorIndex = 36
Set c = Target.Cells(1)


End Sub




"Ted M H" wrote:

Is it possible to set the formatting of a cell based on the current position
of the cursor in the worksheet?
For example, I want to highlight cell C3 if the cursor is currently in cell
G10 and then have that highlight go away when the cursor moves to any other
cell.

Alok,

That's exactly what I needed. I've already got it working and I've modified
it to work in my more complex application. I very much appreciate the help.
Thank you so much!

Ted

"Alok" wrote:

Hi Ted
I understand now.
Go back into the code of the sheet like I have explained. Delete what you
have there and replace with the following

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells(1).Address = "$G$10" Then
Range("$C$3").Interior.ColorIndex = 36
Else
Range("$C$3").Interior.ColorIndex = xlNone
End If
End Sub

Alok

"Ted M H" wrote:

Hi Alok,

Thanks for your patience. I plugged the code in as you instructed and it's
pretty cool. However, it is highlighting whatever cell the cursor is placed
in rather than the cell I want it to highlight. In my simple example, here's
what I'm trying to do:
If the cursor is in cell G10, I want cell C3 to be highlighted. If the
cursor is in any cell other than G10 I do not want cell C3 to be highlighted.
My actual application will be more complicated than this, but I figure if I
can get something that will do this simple function, then I can work with it
on a trial and error basis to get it to do the more complex functions
(Although I'm not a coder I am able to edit macros, albeit not very
efficiently).

Ted

"Alok" wrote:

Hi Ted,

It is just a little more complicated than that.

First off I gave you the code because in my view there is no Conditional
formatting that can do what you are asking for.

I will try and explain what you need to do
Right Click on the tab of the sheet on which you want to put this feature
Paste the code in the window that you see. (Just to clarify the window will
have two drop down boxes - one reading (Declarations) and the other
(General). There may alreay be a line of text reading "Option Explicit". If
there is such a line you will paste the code I sent you below that line (one
line below or 10 lines below, does not matter) otherwise you will paste it
anywhere in that window.

That is all there is to it.

Alok



"Ted M H" wrote:

Yikes! Thanks a bunch for the quick reply, Alok. I probably should have
mentioned in my original post that I'm not a coder...I work at the formula
and superficial macro levels, but code throws me for a loop, so to speak.
Any chance you could help me understand how I would take the code that you
have so generously provided and use it in my spreadsheet? Could I somehow
insert it into the conditional formatting dialog box as one of the
conditions, or is it a lot more complicated than that?

"Alok" wrote:

Hi Ted,
I sent you a link which may be too long to handle.

Here is the code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Static c As Range
Static ci As Integer
If Not c Is Nothing Then
c.Interior.ColorIndex = ci
End If
ci = Target.Cells(1).Interior.ColorIndex
Target.Cells(1).Interior.ColorIndex = 36
Set c = Target.Cells(1)


End Sub




"Ted M H" wrote:

Is it possible to set the formatting of a cell based on the current position
of the cursor in the worksheet?
For example, I want to highlight cell C3 if the cursor is currently in cell
G10 and then have that highlight go away when the cursor moves to any other
cell.