Referencing Addresses (2003)

Topic

New Reply

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

Reply | Quote

Replies

(Edited by sdckapr on 27-Feb-08 13:34. Added PS)

application.Goto Worksheets(“Sheet1”).range(“F2”)

Steve
PS Or do you want something like:
worksheets(“Sheet1”).range(worksheets(“Sheet3”).range(“B2”)).select

Reply | Quote

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”).Range(“B2”)).Font.ColorIndex = 3
Worksheets(“Sheet1”).Range(Worksheets(“Sheet3”).Range(“B3”)).Font.ColorIndex = 3

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

Else
Worksheets(“Sheet1”).Range(Worksheets(“Sheet3”).Range(“B2”)).Font.ColorIndex = 35
Worksheets(“Sheet1”).Range(Worksheets(“Sheet3”).Range(“B3”)).Font.ColorIndex = 35
End If
End If

Sheets(“Sheet3”).Range(“A2:B3”).ClearContents

End Sub

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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…

Reply | Quote

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”).Range(“B2”)).Font.ColorIndex = 3
Worksheets(“Sheet1”).Range(Worksheets(“Sheet3”).Range(“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”).Range(“B2”)).Font.ColorIndex = 35
Worksheets(“Sheet1”).Range(Worksheets(“Sheet3”).Range(“B3”)).Font.ColorIndex = 35
End If
End If

End Sub

Best Wishes

Reply | Quote

Good for you!

Reply | Quote