select range with resize
 

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

select range with resize
 

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)

--
Rick (MVP - Excel)


"joemeshuggah" wrote in message
...
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

select range with resize
 

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" wrote:

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)

--
Rick (MVP - Excel)


"joemeshuggah" wrote in message
...
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










.

select range with resize
 

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

--
Rick (MVP - Excel)


"joemeshuggah" wrote in message
...
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" wrote:

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)

--
Rick (MVP - Excel)


"joemeshuggah" wrote in message
...
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










.