Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Referencing Addresses (2003)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29>

    On a worksheet, (Sheet3), I have two cells (B2 and B3) each of which contains an address in the form $F$2. I would like to use these addresses to select the relevant cells on another worksheet in code. That is, use the value in B3 to seIect Range("$F$2") on Sheet1. Is this possible?
    TIA
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Referencing Addresses (2003)

    <P ID="edit" class=small>(Edited by sdckapr on 27-Feb-08 13:34. Added PS)</P>application.Goto Worksheets("Sheet1").range("F2")

    Steve
    PS Or do you want something like:
    worksheets("Sheet1").range(worksheets("Sheet3").ra nge("B2")).select

  3. #3
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referencing Addresses (2003)

    Yes. The latter. Thanks. I am still experiencing difficultes. Would you take a look at the code, please. I will try to explain what I'm trying to achieve.
    I am trying to simulate the memory game where, when to identical symbols are uncovered they remain visible whereas none matches become invisible. On sheet 1, formatted with Wingdings font, I have a 6X6 grid containing 18 pairs of Wingdings whose font colors match the cell background colors. When I select cells, initially the font changes to red thus revealing the symbols. If they match, I want the font color to stay red. If they don't match I want the font color to return to the cell background color thereby rendering them invisible. I think I need a loop somewhere but I have hit a blind spot. Any advice would be appreciated.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    If Intersect(ActiveCell, Range("C2:H7")) Is Nothing Then Exit Sub
    ActiveCell.Font.ColorIndex = 3

    'Add the ActiveCell value and address to sheet3

    Sheets("Sheet3").Range("A65536").End(xlUp).Offset( 1).Value = ActiveCell.Value
    Sheets("Sheet3").Range("B65536").End(xlUp).Offset( 1).Value = ActiveCell.Address
    Application.Wait Now() + 1 / 43200

    'Check that 2 addresses have been added

    If Sheets("Sheet3").Range("C4").Value = 2 Then

    'Check if the two cell values from sheet are the same. If so change font color.

    If Sheets("Sheet3").Range("A2").Value = Sheets("Sheet3").Range("A3") Then
    Worksheets("Sheet1").Range(Worksheets("Sheet3").Ra nge("B2")).Font.ColorIndex = 3
    Worksheets("Sheet1").Range(Worksheets("Sheet3").Ra nge("B3")).Font.ColorIndex = 3

    ' If not the same, change the font color to match the cell color.

    Else
    Worksheets("Sheet1").Range(Worksheets("Sheet3").Ra nge("B2")).Font.ColorIndex = 35
    Worksheets("Sheet1").Range(Worksheets("Sheet3").Ra nge("B3")).Font.ColorIndex = 35
    End If
    End If

    Sheets("Sheet3").Range("A2:B3").ClearContents

    End Sub
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referencing Addresses (2003)

    What does cell C4 do? You don't set its value anywhere. Does it contain a formula?

  5. #5
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referencing Addresses (2003)

    Hi Hans. C4 checks that there are 2 addresses, ie, that two cells have been selected prior to checking for a match. Once the check is complete, B2 and B2 are cleared ready for the next pair. C4 contains =COUNTA(B2:B3).
    Thanks for taking a look.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referencing Addresses (2003)

    You're clearing A2:B3 *each time* the user selects a different cell, so you'll never fill two rows. You'll have to rethink your strategy...

  7. #7
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referencing Addresses (2003)

    Thanks for your time, Hans. I've rethought my strategy as advised having come to the same conclusion just before you posted. I think I have it working now. I split the code into two procedures, one for the Worksheet SelectionChange event and one for the checking of a matching pair in a module, as shown below.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    If Intersect(ActiveCell, Range("C2:H7")) Is Nothing Then Exit Sub
    ActiveCell.Font.ColorIndex = 3

    'Add the ActiveCell value and address to sheet3

    Sheets("Sheet3").Range("A65536").End(xlUp).Offset( 1).Value = ActiveCell.Value
    Sheets("Sheet3").Range("B65536").End(xlUp).Offset( 1).Value = ActiveCell.Address
    Application.Wait Now() + 1 / 43200

    CheckPair
    If Sheets("Sheet3").Range("C4").Value = 2 Then _

    Sheets("Sheet3").Range("A2:B3").ClearContents
    End If
    End Sub

    Sub CheckPair()

    'Check that 2 addresses have been added

    If Sheets("Sheet3").Range("C4").Value = 2 Then

    'Check if the two cell values from sheet are the same. If so change font color.

    If Sheets("Sheet3").Range("A2").Value = Sheets("Sheet3").Range("A3") Then
    Worksheets("Sheet1").Range(Worksheets("Sheet3").Ra nge("B2")).Font.ColorIndex = 3
    Worksheets("Sheet1").Range(Worksheets("Sheet3").Ra nge("B3")).Font.ColorIndex = 3
    Sheets("Sheet3").Range("A2:B3").ClearContents

    ' If not the same, change the font color to match the cell color.

    Else
    Worksheets("Sheet1").Range(Worksheets("Sheet3").Ra nge("B2")).Font.ColorIndex = 35
    Worksheets("Sheet1").Range(Worksheets("Sheet3").Ra nge("B3")).Font.ColorIndex = 35
    End If
    End If

    End Sub

    Best Wishes
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referencing Addresses (2003)

    Good for you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •