conditional formatting excel

conditional formatting excel

Postby Marc Vanzegbroeck » Mon Feb 05, 2018 10:44 am

Hi,

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

FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Marc Vanzegbroeck
 
Posts: 1157
Joined: Mon Oct 17, 2005 5:41 am
Location: Belgium

Re: conditional formatting excel

Postby Marc Vanzegbroeck » Thu Aug 06, 2020 1:36 pm

Hi,

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

FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Marc Vanzegbroeck
 
Posts: 1157
Joined: Mon Oct 17, 2005 5:41 am
Location: Belgium

Re: conditional formatting excel

Postby ADutheil » Thu Aug 06, 2020 2:33 pm

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

André Dutheil
FWH 13.04 + HB 3.2 + MSVS 10
ADutheil
 
Posts: 368
Joined: Sun May 31, 2009 6:25 pm
Location: Salvador - Bahia - Brazil

Re: conditional formatting excel

Postby Marc Venken » Thu Aug 06, 2020 2:37 pm

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 Venken
Using: FWH 23.04 with Harbour
User avatar
Marc Venken
 
Posts: 1338
Joined: Tue Jun 14, 2016 7:51 am
Location: Belgium

Re: conditional formatting excel

Postby Marc Vanzegbroeck » Thu Aug 06, 2020 3: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
Image
Regards,
Marc

FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Marc Vanzegbroeck
 
Posts: 1157
Joined: Mon Oct 17, 2005 5:41 am
Location: Belgium

Re: conditional formatting excel

Postby ADutheil » Thu Aug 06, 2020 4:20 pm

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
Regards,

André Dutheil
FWH 13.04 + HB 3.2 + MSVS 10
ADutheil
 
Posts: 368
Joined: Sun May 31, 2009 6:25 pm
Location: Salvador - Bahia - Brazil

Re: conditional formatting excel

Postby Marc Vanzegbroeck » Thu Aug 06, 2020 8:58 pm

Thank you, I will try it
Regards,
Marc

FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Marc Vanzegbroeck
 
Posts: 1157
Joined: Mon Oct 17, 2005 5:41 am
Location: Belgium

Re: conditional formatting excel

Postby Marc Vanzegbroeck » Thu Aug 06, 2020 9:54 pm

André,

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

ADutheil wrote: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
Regards,
Marc

FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Marc Vanzegbroeck
 
Posts: 1157
Joined: Mon Oct 17, 2005 5:41 am
Location: Belgium

Re: conditional formatting excel

Postby Marc Vanzegbroeck » Tue Jan 12, 2021 3: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: 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?
Regards,
Marc

FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Marc Vanzegbroeck
 
Posts: 1157
Joined: Mon Oct 17, 2005 5:41 am
Location: Belgium

Re: conditional formatting excel

Postby Marc Vanzegbroeck » Tue Jan 12, 2021 4:02 pm

Hi,

I found it :D
Code: Select all  Expand view
oCond1 := oRng:FormatConditions:Add(xlCellValue, xlEqual, "=$B$2")
Regards,
Marc

FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Marc Vanzegbroeck
 
Posts: 1157
Joined: Mon Oct 17, 2005 5:41 am
Location: Belgium


Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: nageswaragunupudi and 12 guests