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
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
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
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
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
__________________________