Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locating Matches & Highlighting Background
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("A1:D4") 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locating Matches & Highlighting Background
I am not sure why you need to write a macro, this seems to be overcomplicating the process In a cell lets say F3 create data,validation, list from somewhere else in the spreadsheet, this will be your dropdownlist In cell A1 goto format, conditional formating, choose formula is and type =isnumber(search($F$3,A1)) and choose a colour to change the cells to highlight them Then just copy this format by copy, paste special, formats to the other cells a1:d4 Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=573595 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locating Matches & Highlighting Background
Dav,
The main reason I would like to do this in VBA code is that the spreadsheet is used by a group of undisciplined users who very often will blow away a whole section of a spreadsheet including the formating using the copy and paste. If I control it thru VBA code I can have a modicum of control. If you can help further, I would appreciate it very much. Thanks' Old Dog' Learning new tricks. Dav wrote: I am not sure why you need to write a macro, this seems to be overcomplicating the process In a cell lets say F3 create data,validation, list from somewhere else in the spreadsheet, this will be your dropdownlist In cell A1 goto format, conditional formating, choose formula is and type =isnumber(search($F$3,A1)) and choose a colour to change the cells to highlight them Then just copy this format by copy, paste special, formats to the other cells a1:d4 Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=573595 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locating amesaage | Excel Discussion (Misc queries) | |||
Lost highlighting and font colors; background colors on web pages | Excel Discussion (Misc queries) | |||
Locating | New Users to Excel | |||
Highlighting blanks via GO TO SPECIAL is not highlighting blank cells - HELP, I'm totally stuck. | Excel Discussion (Misc queries) | |||
Check box text background remains white, does not match general background | Excel Programming |