conditional formatting excel

[Marc Vanzegbroeck]

Hi,

Is there a way to add conditional formatting to a cell in excel via FWH?

[Marc Vanzegbroeck]

Hi,

Does anyone has already add a conditional formatting to an excel-sheet with FWH?

[ADutheil]

Do you mean this kind of code?

Code: Select all  Expand view

    oSheet:Cells( nL,  4 ):Interior:ColorIndex := if( hora->HSAI < "04:00", Verde, Vermelho )
    oSheet:Cells( nL,  5 ):value := hora->H1PV
    oSheet:Cells( nL,  6 ):value := hora->HUPV
    oSheet:Cells( nL,  6 ):Interior:ColorIndex := if( hora->HUPV < "06:31", Verde, Vermelho )
    oSheet:Cells( nL,  7 ):value := if( "=F" + allTrim( str( nL ) ) > "E" + allTrim( str( nL ) ), "=F" + allTrim( str( nL ) ) + "-E" + allTrim( str( nL ) ), "=F" + allTrim( str( nL ) ) + "-E" + allTrim( str( nL ) ) + "+24" )// "=F" + allTrim( str( nL ) ) + "-E" + allTrim( str( nL ) )
    oSheet:Cells( nL,  8 ):value := if( "=F" + allTrim( str( nL ) ) > "D" + allTrim( str( nL ) ), "=F" + allTrim( str( nL ) ) + "-D" + allTrim( str( nL ) ), "=F" + allTrim( str( nL ) ) + "-D" + allTrim( str( nL ) ) + "+24" ) //"=F" + allTrim( str( nL ) ) + "-D" + allTrim( str( nL ) )
 

[Marc Venken]

Maybe this can give also a clue ...

Code: Select all  Expand view

oExcel := CreateObject( "Excel.Application" )
oExcel:WorkBooks:Add()

oAs := oExcel:Activesheet()

oAs:Cells:Font:Name := "Calibri"
oAs:Cells:Font:Size := 11

oAs:Columns( 1 ):ColumnWidth := 17
oAs:Columns( 2 ):ColumnWidth := 150

oAs:Cells( 3, 1 ):Value := "Prog"
oAs:Cells( 3, 2 ):Value := "Note"

n = 1
for n = 1 to 2
 oAs:Cells(3,n):Borders(7):LineStyle := 1
 oAs:Cells(3,n):Borders(8):LineStyle := 1
next

Use archivio
go top
n = 4
do while !eof()
sysrefresh()
    oAs:Cells( n, 1 ):Value := archivio->c1)   // prog
    oAs:Cells( n, 2 ):Value := archivio->c2)   // campo note
    n = n+1
    skip
enddo

n1 = 1
for n1 = 1 to 2
 oAs:Cells(n-1,n1):Borders(9):LineStyle := 1  
next

oAs:Columns( "A:B" ):WrapText = .T.

/*
oAs:Name := "NC"
* oAs:Columns( "A:T" ):AutoFit()
oAs:Columns( "A:Z" ):VerticalAlignment := -4108
oAs:Columns( "A:Z" ):HorizontalAlignment := -4108
oAs:Columns( "C:C" ):HorizontalAlignment := -4131
oAs:Columns( "Q:Q" ):HorizontalAlignment := -4131
 
oAs:Columns( "W:W" ):WrapText = .F.

oAs:Range("I2:Q2"):interior:color := rgb(184,204,228)
oAs:Range("I3:Q3"):interior:color := rgb(217,217,217)
oAs:Range("A3:H3"):interior:color := rgb(54,96,146)
oAs:Range("A3:H3"):font:color := rgb(255,255,255)
*/

 oExcel:visible := .T

 

[Marc Vanzegbroeck]

Hello,

Thank you for the responce, but this is how I already do it.
Testing on a value in the program, and according of the result, setting a color of the cell.

But what I want to do is add 'conditional formatting' on cells, so I someone change a value in the excel, the color of the cell change according to the formule.

Like the 'conditional formatting' button in excel

[ADutheil]

I'd try something like this:

Code: Select all  Expand view

oRng := oSheet:Range( oSheet:Cells( 1, 1 ), oSheet:Cells( 1, 7 ) )
oCond1 := oRng:FormatConditions:Add(xlCellValue, xlGreater, "=80")
oCond2 := oRng:FormatConditions:Add(xlCellValue, xlLess, "=50")
WITH OBJECT oCond1
    :Font:Bold := .T.
    :Font:Color = azul
END

WITH OBJECT oCond2
    :Font:Bold := .T.
    :Font:Color = vermelho
END
 

NOT TESTED

[Marc Vanzegbroeck]

Thank you, I will try it

[Marc Vanzegbroeck]

André,

Thank you, I have tested it, and it's working

I'd try something like this:

Code: Select all  Expand view

oRng := oSheet:Range( oSheet:Cells( 1, 1 ), oSheet:Cells( 1, 7 ) )
oCond1 := oRng:FormatConditions:Add(xlCellValue, xlGreater, "=80")
oCond2 := oRng:FormatConditions:Add(xlCellValue, xlLess, "=50")
WITH OBJECT oCond1
    :Font:Bold := .T.
    :Font:Color = azul
END

WITH OBJECT oCond2
    :Font:Bold := .T.
    :Font:Color = vermelho
END
 

NOT TESTED

[Marc Vanzegbroeck]

Hi,

I'm using this FormatConditions, and it's working fine, but now I want to use instead of for example testing if the content is "Tekst" like this

Code: Select all  Expand view

oCond1 := oRng:FormatConditions:Add(xlCellValue, xlEqual, "Tekst")

I want to compare it with another field.

Using

Code: Select all  Expand view

oCond1 := oRng:FormatConditions:Add(xlCellValue, xlEqual, "$B$2")

it check if the field is "$B$2" , and not that the field is equal to the cell B2

How can I do this?

[Marc Vanzegbroeck]

Hi,

I found it

Code: Select all  Expand view

oCond1 := oRng:FormatConditions:Add(xlCellValue, xlEqual, "=$B$2")