select range with resize

  • Thread starter Thread starter joemeshuggah
  • Start date Start date
J

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
 
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)
 
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.
 
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
 
Back
Top