Is there a formula to show which cell is currently selected

I want to select a cell and then use conditional formatting to highlight
other cells based on the value of the selected cell.

On Mon, 8 May 2006 03:26:01 -0700, Chris
<Chris@discussions.microsoft.com> wrote:

>I want to select a cell and then use conditional formatting to highlight
>other cells based on the value of the selected cell.

With A1 as the selected cell and C1 as one of your 'other cells, put
conditional formats in C1

Format-->Conditional Formatting
In the Condition 1 first drop down box, choose the 'Formula Is' option
and in the second drop down put =A1=1 and then choose a format
colour.

Add another condition with the Add>> button, do the same sort of thing
with Condition 2 and put in the second drop down box =A1=2 then
choose a different format colour.

Now change A1 to 1 or 2 and observe C1 changes its format.

HTH


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Thanks Richard, you've answered the question as I wrote it, however looking
back I didn't make myself clear. What I would like to do is to format the
cells based on the value of whichever cell is selected, only I don't know how
to find the value of the currently selected cell.

For example, my worksheet might have the following values in column A:

Germany
Costa Rica
Poland
Equador

..... and the following in column C:
Germany Costa Rica
Poland Equador

In this example if I click on A1 then I would want C1 to be highlighted
(because A1 contains "Germany"). Likewise if A3 is selected then C2 is
highlighted.

The problem is that the values in column A will change so I need to test the
value of the selected cell.

Thanks.

"Richard Buttrey" wrote:

> On Mon, 8 May 2006 03:26:01 -0700, Chris
> <Chris@discussions.microsoft.com> wrote:
>
> >I want to select a cell and then use conditional formatting to highlight
> >other cells based on the value of the selected cell.
>
> With A1 as the selected cell and C1 as one of your 'other cells, put
> conditional formats in C1
>
> Format-->Conditional Formatting
> In the Condition 1 first drop down box, choose the 'Formula Is' option
> and in the second drop down put =A1=1 and then choose a format
> colour.
>
> Add another condition with the Add>> button, do the same sort of thing
> with Condition 2 and put in the second drop down box =A1=2 then
> choose a different format colour.
>
> Now change A1 to 1 or 2 and observe C1 changes its format.
>
> HTH
>
>
> __
> Richard Buttrey
> Grappenhall, Cheshire, UK
> __________________________
>

On Mon, 8 May 2006 05:35:02 -0700, Chris
<Chris@discussions.microsoft.com> wrote:

>Thanks Richard, you've answered the question as I wrote it, however looking
>back I didn't make myself clear. What I would like to do is to format the
>cells based on the value of whichever cell is selected, only I don't know how
>to find the value of the currently selected cell.
>
>For example, my worksheet might have the following values in column A:
>
>Germany
>Costa Rica
>Poland
>Equador
>
>.... and the following in column C:
>Germany Costa Rica
>Poland Equador
>
>In this example if I click on A1 then I would want C1 to be highlighted
>(because A1 contains "Germany"). Likewise if A3 is selected then C2 is
>highlighted.
>
>The problem is that the values in column A will change so I need to test the
>value of the selected cell.
>
>Thanks.
>
Ah, OK - understood.

One way would be to use the Worksheet Selection change event.

So with the four example countries you've noted in A1:A4, and a list
of the same four countries in say C1:C12 use the following Sheet
Selection change procedure


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim stMySel As String
Dim rMyCell As Range

If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target = "" Then Exit Sub
Range("C:C").ClearFormats
stMySel = ActiveCell.Text
For Each rMyCell In Range("C:C")
If rMyCell.Value = stMySel Then
rMyCell.Font.Bold = True
rMyCell.Interior.ColorIndex = 3
End If
Next
End If
End Sub


Selecting a value in column A will apply a bold font and red
background to the equivalent cells in column C. Change the formatting
to suit your particular requirements.

HTH


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Thanks Richard, that worked perfectly.

Chris.

"Richard Buttrey" wrote:

> On Mon, 8 May 2006 05:35:02 -0700, Chris
> <Chris@discussions.microsoft.com> wrote:
>
> >Thanks Richard, you've answered the question as I wrote it, however looking
> >back I didn't make myself clear. What I would like to do is to format the
> >cells based on the value of whichever cell is selected, only I don't know how
> >to find the value of the currently selected cell.
> >
> >For example, my worksheet might have the following values in column A:
> >
> >Germany
> >Costa Rica
> >Poland
> >Equador
> >
> >.... and the following in column C:
> >Germany Costa Rica
> >Poland Equador
> >
> >In this example if I click on A1 then I would want C1 to be highlighted
> >(because A1 contains "Germany"). Likewise if A3 is selected then C2 is
> >highlighted.
> >
> >The problem is that the values in column A will change so I need to test the
> >value of the selected cell.
> >
> >Thanks.
> >
> Ah, OK - understood.
>
> One way would be to use the Worksheet Selection change event.
>
> So with the four example countries you've noted in A1:A4, and a list
> of the same four countries in say C1:C12 use the following Sheet
> Selection change procedure
>
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Dim stMySel As String
> Dim rMyCell As Range
>
> If Not Intersect(Target, Range("A:A")) Is Nothing Then
> If Target = "" Then Exit Sub
> Range("C:C").ClearFormats
> stMySel = ActiveCell.Text
> For Each rMyCell In Range("C:C")
> If rMyCell.Value = stMySel Then
> rMyCell.Font.Bold = True
> rMyCell.Interior.ColorIndex = 3
> End If
> Next
> End If
> End Sub
>
>
> Selecting a value in column A will apply a bold font and red
> background to the equivalent cells in column C. Change the formatting
> to suit your particular requirements.
>
> HTH
>
>
> __
> Richard Buttrey
> Grappenhall, Cheshire, UK
> __________________________
>