software

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
gt; wrote:

gt;I want to select a cell and then use conditional formatting to highlight
gt;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--gt;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 Addgt;gt; 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:
GermanyCosta Rica
PolandEquador

In this example if I click on A1 then I would want C1 to be highlighted
(because A1 contains quot;Germanyquot;). 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.

quot;Richard Buttreyquot; wrote:

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

On Mon, 8 May 2006 05:35:02 -0700, Chris
gt; wrote:

gt;Thanks Richard, you've answered the question as I wrote it, however looking
gt;back I didn't make myself clear. What I would like to do is to format the
gt;cells based on the value of whichever cell is selected, only I don't know how
gt;to find the value of the currently selected cell.
gt;
gt;For example, my worksheet might have the following values in column A:
gt;
gt;Germany
gt;Costa Rica
gt;Poland
gt;Equador
gt;
gt;.... and the following in column C:
gt;GermanyCosta Rica
gt;PolandEquador
gt;
gt;In this example if I click on A1 then I would want C1 to be highlighted
gt;(because A1 contains quot;Germanyquot;). Likewise if A3 is selected then C2 is
gt;highlighted.
gt;
gt;The problem is that the values in column A will change so I need to test the
gt;value of the selected cell.
gt;
gt;Thanks.
gt;
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 procedurePrivate Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim stMySel As String
Dim rMyCell As Range

If Not Intersect(Target, Range(quot;A:Aquot;)) Is Nothing Then
If Target = quot;quot; Then Exit Sub
Range(quot;C:Cquot;).ClearFormats
stMySel = ActiveCell.Text
For Each rMyCell In Range(quot;C:Cquot;)
If rMyCell.Value = stMySel Then
rMyCell.Font.Bold = True
rMyCell.Interior.ColorIndex = 3
End If
Next
End If
End SubSelecting 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.

quot;Richard Buttreyquot; wrote:

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