Ask Your Question

Macro to change colour of some words in a string in a cell in Calc ? [closed]

asked 2014-10-17 08:59:33 +0200

daniel b. gravatar image

updated 2020-07-21 21:06:15 +0200

Alex Kemp gravatar image

The results of a word search in a range in a LibreOffice Calc sheet are displayed on A1...A100 cells. Each cell may contain a paragraph of text within which there is the search result (somewhere). It's sometimes hard to quickly see where the result is. How can one change the colour and weight only for the searched word not the entire cell?

Something like this: .....[2014_10_15 order delivered ] [2014_10_13 received DK instruments] [2014_10_05 : DK order placed, payment.....]


edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-05 00:00:57.964115


Of course, using a macro... :)

daniel b. gravatar imagedaniel b. ( 2014-10-17 09:03:30 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2014-10-18 07:22:43 +0200

daniel b. gravatar image

Found a solution. Here it is, in case anyone hits the same wall as I had :)

Sub Main

Dim oDoc as Object, oSheet as Object, oCell as Object
Dim oTextCursor as Object
    oCell.String="The Moon is in synchronous rotation with Earth, always showing the same face with its near side marked by dark volcanic maria that fill between the bright ancient crustal highlands and the prominent impact craters. It is the second-brightest object in the sky, after the Sun (measured by illuminance on the surface of the Earth)"
    with oCell
        .CharFontName = "Arial"
        '.CharPosture =
    end with

str_length = len (oCell.String)

str_searched = "ea"
str_searched_length = len (str_searched)

oTextCursor = oCell.createTextCursor()

for i = 1 to str_length
    found = instr (i, oCell.String, str_searched)
    if found > 0 then
            With oTextCursor
                .gotoStart( False )
                .goRight(found-1 , False )  
                .goRight(str_searched_length, True )
                .gotoEnd( False )
            End with
        i = i + found+str_searched_length
next i

End Sub

edit flag offensive delete link more

Question Tools



Asked: 2014-10-17 08:59:33 +0200

Seen: 1,299 times

Last updated: Oct 18 '14