Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find entire string then row background red
Hi Everyone
I dont know how easy this is but I wondered if this can be done in code. I need a macro to look down column A and search for an extract string of text. Everytime it finds a match it is then to highlight the row and format the background of the row a specified colour. I want to be able to use this macro for different string to colour all my data. I hope someone can help, Many thanks Andrea |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
find entire string then row background red
Hi Andrea,
Hope the below code helps!!! Sub test() Dim i As Integer Dim mSearch As String On Error GoTo HandleError 'This is your search string mSearch = "test" 'Search for the string in column A i = WorksheetFunction.Match(mSearch, Range("A:A"), 0) 'If found select the entire row Range("" & i & ":" & i & "").Select With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With Range("A" & i).Select HandleError: End Sub -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER!! " wrote: Hi Everyone I dont know how easy this is but I wondered if this can be done in code. I need a macro to look down column A and search for an extract string of text. Everytime it finds a match it is then to highlight the row and format the background of the row a specified colour. I want to be able to use this macro for different string to colour all my data. I hope someone can help, Many thanks Andrea |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
find entire string then row background red
Sub ColorCells()
Dim ans As String, ans1 As Long Dim c As Range, rng2 As Range Dim firstaddress As String With ActiveSheet Set rng2 = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp)) End With ans = InputBox("Enter string to search for") ans1 = Application.InputBox("enter colorIndex number for color", Type:=1) Set c = rng2.Find(What:=ans, _ After:=rng2(rng2.Count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not c Is Nothing Then firstaddress = c.Address Do c.EntireRow.Interior.ColorIndex = ans1 Set c = rng2.FindNext(c) Loop While c.Address < firstaddress End If End Sub Worked for me. -- Regards, Tom Ogilvy " wrote: Hi Everyone I dont know how easy this is but I wondered if this can be done in code. I need a macro to look down column A and search for an extract string of text. Everytime it finds a match it is then to highlight the row and format the background of the row a specified colour. I want to be able to use this macro for different string to colour all my data. I hope someone can help, Many thanks Andrea |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
find entire string then row background red
Depending on how many conditions you have, I believe Conditional
Formatting might be the way to go here. Tom Ogilvy wrote: Sub ColorCells() Dim ans As String, ans1 As Long Dim c As Range, rng2 As Range Dim firstaddress As String With ActiveSheet Set rng2 = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp)) End With ans = InputBox("Enter string to search for") ans1 = Application.InputBox("enter colorIndex number for color", Type:=1) Set c = rng2.Find(What:=ans, _ After:=rng2(rng2.Count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not c Is Nothing Then firstaddress = c.Address Do c.EntireRow.Interior.ColorIndex = ans1 Set c = rng2.FindNext(c) Loop While c.Address < firstaddress End If End Sub Worked for me. -- Regards, Tom Ogilvy " wrote: Hi Everyone I dont know how easy this is but I wondered if this can be done in code. I need a macro to look down column A and search for an extract string of text. Everytime it finds a match it is then to highlight the row and format the background of the row a specified colour. I want to be able to use this macro for different string to colour all my data. I hope someone can help, Many thanks Andrea |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
find entire string then row background red
I the OP doesn't mind the overhead and only needs 3 colors plus the
background color, I would agree. If he will be changing colors or doesn't want the overhead (or is using conditional formatting for some other purpose) or needs more than 3, then perhaps not. Nonetheless go ahead and lay it out. -- Regards, Tom Ogilvy "JW" wrote: Depending on how many conditions you have, I believe Conditional Formatting might be the way to go here. Tom Ogilvy wrote: Sub ColorCells() Dim ans As String, ans1 As Long Dim c As Range, rng2 As Range Dim firstaddress As String With ActiveSheet Set rng2 = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp)) End With ans = InputBox("Enter string to search for") ans1 = Application.InputBox("enter colorIndex number for color", Type:=1) Set c = rng2.Find(What:=ans, _ After:=rng2(rng2.Count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not c Is Nothing Then firstaddress = c.Address Do c.EntireRow.Interior.ColorIndex = ans1 Set c = rng2.FindNext(c) Loop While c.Address < firstaddress End If End Sub Worked for me. -- Regards, Tom Ogilvy " wrote: Hi Everyone I dont know how easy this is but I wondered if this can be done in code. I need a macro to look down column A and search for an extract string of text. Everytime it finds a match it is then to highlight the row and format the background of the row a specified colour. I want to be able to use this macro for different string to colour all my data. I hope someone can help, Many thanks Andrea |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
find entire string then row background red
Hi Tom
I wondered if there is a way for your original macro to lookup the exact values from sheet2 column A and then the colorindex value from sheet2 column B. This would save me alot of time. Please let me know if your macro can be revised to show this. Thanks alot Andrea On Sep 19, 3:22 pm, Tom Ogilvy wrote: Sub ColorCells() Dim ans As String, ans1 As Long Dim c As Range, rng2 As Range Dim firstaddress As String With ActiveSheet Set rng2 = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp)) End With ans = InputBox("Enter string to search for") ans1 = Application.InputBox("enter colorIndex number for color", Type:=1) Set c = rng2.Find(What:=ans, _ After:=rng2(rng2.Count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not c Is Nothing Then firstaddress = c.Address Do c.EntireRow.Interior.ColorIndex = ans1 Set c = rng2.FindNext(c) Loop While c.Address < firstaddress End If End Sub Worked for me. -- Regards, Tom Ogilvy " wrote: Hi Everyone I dont know how easy this is but I wondered if this can be done in code. I need a macro to look down column A and search for an extract string of text. Everytime it finds a match it is then to highlight the row and format the background of the row a specified colour. I want to be able to use this macro for different string to colour all my data. I hope someone can help, Many thanks Andrea- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EXCEL function;find string in entire column & return cell referenc | Excel Worksheet Functions | |||
Background or Header picture should cover entire A4 sheet when pri | Excel Discussion (Misc queries) | |||
Sum of Len(string) for an entire row | Excel Programming | |||
Background colour grey on entire worksheet | Excel Discussion (Misc queries) | |||
backwards find function to find character in a string of text | Excel Programming |