FiveTech Software tech support forums

www.FiveTechSoft.com
https://forums.fivetechsoft.com/

conditional formatting excel

https://forums.fivetechsoft.com/viewtopic.php?f=3&t=35232

Page 1 of 1

conditional formatting excel

Posted: Mon Feb 05, 2018 10:44 am
by Marc Vanzegbroeck
Hi,

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

Re: conditional formatting excel

Posted: Thu Aug 06, 2020 1:36 pm
by Marc Vanzegbroeck
Hi,

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

Re: conditional formatting excel

Posted: Thu Aug 06, 2020 2:33 pm
by ADutheil
Do you mean this kind of code?

Code: Select all

    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 ) )
 

Re: conditional formatting excel

Posted: Thu Aug 06, 2020 2:37 pm
by Marc Venken
Maybe this can give also a clue ...

Code: Select all


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

 

Re: conditional formatting excel

Posted: Thu Aug 06, 2020 3:08 pm
by 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
Image

Re: conditional formatting excel

Posted: Thu Aug 06, 2020 4:20 pm
by ADutheil
I'd try something like this:

Code: Select all

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

Re: conditional formatting excel

Posted: Thu Aug 06, 2020 8:58 pm
by Marc Vanzegbroeck
Thank you, I will try it

Re: conditional formatting excel

Posted: Thu Aug 06, 2020 9:54 pm
by Marc Vanzegbroeck
André,

Thank you, I have tested it, and it's working :D
ADutheil wrote:I'd try something like this:

Code: Select all

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

Re: conditional formatting excel

Posted: Tue Jan 12, 2021 3:26 pm
by 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

oCond1 := oRng:FormatConditions:Add(xlCellValue, xlEqual, "Tekst")
I want to compare it with another field.

Using

Code: Select all

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?

Re: conditional formatting excel

Posted: Tue Jan 12, 2021 4:02 pm
by Marc Vanzegbroeck
Hi,

I found it :D

Code: Select all

oCond1 := oRng:FormatConditions:Add(xlCellValue, xlEqual, "=$B$2")
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited