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
__________________________