Ask Your Question
0

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 2014-10-18 07:24:39 +0200

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.....]

Thanks.

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

Comments

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
0

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
    oDoc=ThisComponent
    oSheet=oDoc.getSheets().getByindex(0)
    oCell=oSheet.getCellByPosition(0,1)
    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 = com.sun.star.awt.FontSlant.ITALIC
        .CharHeight=12      
    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 )
                .setPropertyValue("CharColor",0099000)  
                .gotoEnd( False )
            End with
        i = i + found+str_searched_length
    endif
next i

End Sub

edit flag offensive delete link more

Question Tools

1 follower

Stats

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

Seen: 1,097 times

Last updated: Oct 18 '14