Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditionally-like formatting just a part of a cell
Hello,
assume my cells ( e.g. A1:E10) are filled with words ( i.e. one word per one cell); I want to find specific character groups, so if I am looking for a group 'eel' and a cells contains 'peeling', I want that "eel" part to get formatted. How could I go about this? In the next step, I would like to obtain the character group to search for from a specific cell, like in - "take the contents of F1 and search for it through A1:E10, if it is found, format that part of a cell contents". Can anyone help? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditionally-like formatting just a part of a cell
Hi Mac,
The following takes the contents of cell F1 and finds all occurrences of the string in the specified range. There are 2 formatting lines of code. One formats the found string to Bold and the other formats it to Red so you finish up with Bold Red. Sub FindAndFormat() Dim rngToSearch As Range Dim rngToFind As Range Dim strToFind As String Dim strFirstAddr As String Dim intFirstChr As Integer Dim intLenStr As Integer strToFind = Sheets("Sheet1").Range("F1") intLenStr = Len(strToFind) With Sheets("Sheet1") Set rngToSearch = .Range("A1:E10") End With With rngToSearch Set rngToFind = .Find(What:="eel", _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If Not rngToFind Is Nothing Then strFirstAddr = rngToFind.Address Do rngToFind.Characters _ (InStr(1, rngToFind.Value, strToFind), intLenStr) _ .Font.Bold = True rngToFind.Characters _ (InStr(1, rngToFind.Value, strToFind), intLenStr) _ .Font.Color = vbRed Set rngToFind = .FindNext(rngToFind) Loop While Not rngToFind Is Nothing _ And rngToFind.Address < strFirstAddr End If End With End Sub -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditionally-like formatting just a part of a cell
My apologies Mac,
I forgot to change the hard coded "eel" in the find code to the variable that I created. Use the following instead. Sub FindAndFormat() Dim rngToSearch As Range Dim rngToFind As Range Dim strToFind As String Dim strFirstAddr As String Dim intFirstChr As Integer Dim intLenStr As Integer strToFind = Sheets("Sheet1").Range("F1") intLenStr = Len(strToFind) With Sheets("Sheet1") Set rngToSearch = .Range("A1:E10") End With With rngToSearch Set rngToFind = .Find(What:=strToFind, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If Not rngToFind Is Nothing Then strFirstAddr = rngToFind.Address Do rngToFind.Characters _ (InStr(1, rngToFind.Value, strToFind), intLenStr) _ .Font.Bold = True rngToFind.Characters _ (InStr(1, rngToFind.Value, strToFind), intLenStr) _ .Font.Color = vbRed Set rngToFind = .FindNext(rngToFind) Loop While Not rngToFind Is Nothing _ And rngToFind.Address < strFirstAddr End If End With End Sub -- Regards, OssieMac |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditionally-like formatting just a part of a cell
Excellent, OssieMac! That is it, thank you! One more point - what would be
the best way to unset any previous formatting before proceeding with the next? Is there e.g. a function to do like 'take range a1:e10 and clear all formatting'? "OssieMac" wrote: My apologies Mac, I forgot to change the hard coded "eel" in the find code to the variable that I created. Use the following instead. Sub FindAndFormat() Dim rngToSearch As Range Dim rngToFind As Range Dim strToFind As String Dim strFirstAddr As String Dim intFirstChr As Integer Dim intLenStr As Integer strToFind = Sheets("Sheet1").Range("F1") intLenStr = Len(strToFind) With Sheets("Sheet1") Set rngToSearch = .Range("A1:E10") End With With rngToSearch Set rngToFind = .Find(What:=strToFind, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If Not rngToFind Is Nothing Then strFirstAddr = rngToFind.Address Do rngToFind.Characters _ (InStr(1, rngToFind.Value, strToFind), intLenStr) _ .Font.Bold = True rngToFind.Characters _ (InStr(1, rngToFind.Value, strToFind), intLenStr) _ .Font.Color = vbRed Set rngToFind = .FindNext(rngToFind) Loop While Not rngToFind Is Nothing _ And rngToFind.Address < strFirstAddr End If End With End Sub -- Regards, OssieMac |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditionally-like formatting just a part of a cell
Hi Mac,
Insert the following 2 lines of code .Font.Bold = False .Font.ColorIndex = 0 in between these 2 lines of code With rngToSearch Set rngToFind = .Find(What:=strToFind, _ -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditionally formatting a cell based on other cell values | Excel Worksheet Functions | |||
Conditionally formatting just part of a cell contents - how? | Excel Worksheet Functions | |||
how can I conditionally format a cell Part 2 | Excel Discussion (Misc queries) | |||
Conditionally formatting highest valued cell? | Excel Worksheet Functions | |||
Conditionally Formatting a Cell | Excel Programming |