select range with resize

[joemeshuggah]

i am trying to build a macro that will highlight the cell double clicked and
the cell to the right of it , but cant seem to be able to get the resize to
work...here is what i have without the resize...


Option Explicit







Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

Dim TheRange As Range
Dim oCell As Range
Dim Test As String





Test = Target.Value



Set TheRange = Range("A1:e200").SpecialCells( _
xlCellTypeConstants, xlTextValues)

For Each oCell In TheRange
If oCell.Text <> Test Then
oCell.Font.Bold = False
oCell.Interior.ColorIndex = 0


End If
Next oCell


For Each oCell In TheRange
If oCell.Text = Test Then
oCell.Font.Bold = True
oCell.Interior.ColorIndex = 5

End If

Next oCell

Set TheRange = Range("A1:b200").SpecialCells( _
xlCellTypeConstants, xlNumbers)

For Each oCell In TheRange
If oCell.Text <> Test Then
oCell.Font.Bold = False
oCell.Interior.ColorIndex = 0

End If
Next oCell


For Each oCell In TheRange
If oCell.Text = Test Then
oCell.Font.Bold = True
oCell.Interior.ColorIndex = 5

End If

Next oCell

Range("A1").Select




End Sub

 

[Rick Rothstein]

It is not completely clear to me where you want this "highlight" to occur
within your code nor exactly what you mean by "highlight" (select? color?
border? something else?); however, the cell that was doubled-clicked and the
cell next to it on the right would be specified like this...

Target.Resize(1, 2)

 

[joemeshuggah]

sorry for the ambiguity...here is what i am trying to accomplish:

column a has a rep name, column b has the rep total. when a user double
clicks the reps name, the reps name and their total (in the adjacent cell)
would change to bold face type with a blue interior.

 

[Rick Rothstein]

Give this BeforeDoubleClick event code a try...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Column = 1 Then
Cancel = True
Columns(1).Resize(, 2).ClearFormats
With Target.Resize(1, 2)
.Font.Bold = True
.Interior.ColorIndex = 5
End With
End If
End Sub