topic-35232

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

conditional formatting excel
Posted: Mon Feb 05, 2018 10:44 AM

Hi,

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

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: conditional formatting excel
Posted: Thu Aug 06, 2020 01:36 PM

Hi,

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

ADutheil

Posts: 368

Joined: Sun May 31, 2009 06:25 PM

Re: conditional formatting excel
Posted: Thu Aug 06, 2020 02:33 PM

Do you mean this kind of code?

Code (fw): Select all Collapse

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

Regards,

André Dutheil

FWH 13.04 + HB 3.2 + MSVS 10

Marc Venken

Posts: 1487

Joined: Tue Jun 14, 2016 07:51 AM

Re: conditional formatting excel
Posted: Thu Aug 06, 2020 02:37 PM

Maybe this can give also a clue ...

Code (fw): Select all Collapse

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"):color := rgb(184,204,228)
oAs:Range("I3:Q3"):color := rgb(217,217,217)
oAs:Range("A3:H3"):color := rgb(54,96,146)
oAs:Range("A3:H3"):color := rgb(255,255,255)
*/

 oExcel:visible := .T

Marc Venken

Using: FWH 23.08 with Harbour

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: conditional formatting excel
Posted: Thu Aug 06, 2020 03:08 PM

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

Posts: 368

Joined: Sun May 31, 2009 06:25 PM

Re: conditional formatting excel
Posted: Thu Aug 06, 2020 04:20 PM

I'd try something like this:

Code (fw): Select all Collapse

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

Regards,

André Dutheil

FWH 13.04 + HB 3.2 + MSVS 10

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: conditional formatting excel
Posted: Thu Aug 06, 2020 08:58 PM

Thank you, I will try it

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: conditional formatting excel
Posted: Thu Aug 06, 2020 09:54 PM

André,

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

ADutheil wrote:I'd try something like this:

Code (fw): Select all Collapse

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

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: conditional formatting excel
Posted: Tue Jan 12, 2021 03:26 PM

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 (fw): Select all Collapse

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

I want to compare it with another field.

Using

Code (fw): Select all Collapse

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

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: conditional formatting excel
Posted: Tue Jan 12, 2021 04:02 PM

Hi,

I found it

Code (fw): Select all Collapse

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

FiveTech Support Forums

Continue the discussion