O
Old Dog
I am trying to set up a VBA macro that will allow the users to select
an individual from a list. Then run a macro that will highlight all the
cells in the given range("A14") that contain the individual's
name. Some of the cells will contain just the individuals name while
others will contain that individual plus another. The selection should
be highlighted upon selection of either name on a cell with two names.
A1-BOB**JACK-------B1-HARRY---------C1-HARRY**JACK-------D1-JOHN------------------E1-BOB
A2-HARRY-----------B2-JOHN----------C2-BOB----------------D2-SUE--------------------E2-HARRY
A3-SUE--------------B3-SUE**JACK----C3-JOHN----------------D3-BOB**JACK-----------E3-SUE
A4-BOB--------------B4-BOB-----------C4-HARRY-------------D4-JOHN------------------E4-JOHN
A5-----------------------------------------------------------------------------------E5JACK
A6
A7-BOB
A8
GOAL: When BOB is selected IN column E1 then the macro is run. The
macro will change the background color in cells A1, A4, B4, C2 & D3
only. While this is the long way around the only issue is that it
doesn't select anything but an exact match. Any suggestion would be
appreciated.
Sub FindTrial2()
'
' Macro recorded 8/19/2006
'
Dim Find_Name As String
Dim x As Variant
Find_Name = ActiveCell
RowCounter = 0
Range("D4").Select
Do Until RowCounter = "51"
ActiveCell.Offset(1, 0).Select
If ActiveCell = Find_Name Then
With Selection.Interior
.ColorIndex = 6
End With
End If
RowCounter = RowCounter + 1
Loop
RowCounter = 0
Range("E4").Select
Do Until RowCounter = "51"
ActiveCell.Offset(1, 0).Select
If ActiveCell = Find_Name Then
With Selection.Interior
.ColorIndex = 6
End With
End If
RowCounter = RowCounter + 1
Loop
RowCounter = 0
Range("F4").Select
Do Until RowCounter = "51"
ActiveCell.Offset(1, 0).Select
If ActiveCell = Find_Name Then
With Selection.Interior
.ColorIndex = 6
End With
End If
RowCounter = RowCounter + 1
Loop
RowCounter = 0
Range("G4").Select
Do Until RowCounter = "51"
ActiveCell.Offset(1, 0).Select
If ActiveCell = Find_Name Then
With Selection.Interior
.ColorIndex = 6
End With
End If
RowCounter = RowCounter + 1
Loop
End Sub
an individual from a list. Then run a macro that will highlight all the
cells in the given range("A14") that contain the individual's
name. Some of the cells will contain just the individuals name while
others will contain that individual plus another. The selection should
be highlighted upon selection of either name on a cell with two names.
A1-BOB**JACK-------B1-HARRY---------C1-HARRY**JACK-------D1-JOHN------------------E1-BOB
A2-HARRY-----------B2-JOHN----------C2-BOB----------------D2-SUE--------------------E2-HARRY
A3-SUE--------------B3-SUE**JACK----C3-JOHN----------------D3-BOB**JACK-----------E3-SUE
A4-BOB--------------B4-BOB-----------C4-HARRY-------------D4-JOHN------------------E4-JOHN
A5-----------------------------------------------------------------------------------E5JACK
A6
A7-BOB
A8
GOAL: When BOB is selected IN column E1 then the macro is run. The
macro will change the background color in cells A1, A4, B4, C2 & D3
only. While this is the long way around the only issue is that it
doesn't select anything but an exact match. Any suggestion would be
appreciated.
Sub FindTrial2()
'
' Macro recorded 8/19/2006
'
Dim Find_Name As String
Dim x As Variant
Find_Name = ActiveCell
RowCounter = 0
Range("D4").Select
Do Until RowCounter = "51"
ActiveCell.Offset(1, 0).Select
If ActiveCell = Find_Name Then
With Selection.Interior
.ColorIndex = 6
End With
End If
RowCounter = RowCounter + 1
Loop
RowCounter = 0
Range("E4").Select
Do Until RowCounter = "51"
ActiveCell.Offset(1, 0).Select
If ActiveCell = Find_Name Then
With Selection.Interior
.ColorIndex = 6
End With
End If
RowCounter = RowCounter + 1
Loop
RowCounter = 0
Range("F4").Select
Do Until RowCounter = "51"
ActiveCell.Offset(1, 0).Select
If ActiveCell = Find_Name Then
With Selection.Interior
.ColorIndex = 6
End With
End If
RowCounter = RowCounter + 1
Loop
RowCounter = 0
Range("G4").Select
Do Until RowCounter = "51"
ActiveCell.Offset(1, 0).Select
If ActiveCell = Find_Name Then
With Selection.Interior
.ColorIndex = 6
End With
End If
RowCounter = RowCounter + 1
Loop
End Sub