Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I can't select, move or resize an image in Excel?? | Excel Discussion (Misc queries) | |||
Resize Table Range to exclude zero values and Input New Range into achart object | Excel Programming | |||
Select proper charts to resize | Excel Discussion (Misc queries) | |||
Resize Range without Select | Excel Programming | |||
Range.Delete and Range.Resize.Name performance issues | Excel Programming |