Cell Colour based on Value
Cell Colour based on Value
(OP)
thread256-1078020: Change cell or font color based on data value
Hi everyone, i have tried to follow the example in the linked thread but i must be stupid.
I am using frontpage 2003 and the database results wizard.
I have a table extracting data which has the following column headings:-
depot customer contact address1 dateofhire destination etc etc
we have 5 depots, LEY WKS BOS ASP HWD, and i'd like the depot cell colour to be in a different colour according to the value 'LEY WLK' of the depot field, to make it easier for people to associate the work for each depot.
Is this possible, please help, i've been struggling on this for ages.
Hi everyone, i have tried to follow the example in the linked thread but i must be stupid.
I am using frontpage 2003 and the database results wizard.
I have a table extracting data which has the following column headings:-
depot customer contact address1 dateofhire destination etc etc
we have 5 depots, LEY WKS BOS ASP HWD, and i'd like the depot cell colour to be in a different colour according to the value 'LEY WLK' of the depot field, to make it easier for people to associate the work for each depot.
Is this possible, please help, i've been struggling on this for ages.
RE: Cell Colour based on Value
CODE
<body>
<script language="vbscript">
' Set up the test data
dim v(20), depot(5), colour(5)
vcolmax = 20
' Initialize
depot(0) = "LEY": colour(0) = "red"
depot(1) = "WKS": colour(1) = "blue"
depot(2) = "BOS": colour(2) = "green"
depot(3) = "ASP": colour(3) = "cyan"
depot(4) = "HWD": colour(4) = "magenta"
depotmax = 4
' Set up test data
for ii = 0 to vcolmax
v(ii) = depot(cint(rnd() * 5))
next
</script>
<table border="1" cellspacing="1" width="100%" id="AutoNumber1">
<tr>
<th>Depot</th>
<th>Customer</th>
<th>Contact</th>
<th>Address</th>
<th>Date Of Hire</th>
<th>Destination</th>
</tr>
<script language="vbscript">
for i = 1 to vcolmax
document.write "<tr>"
for dd = 0 to depotmax
if v(i) = depot(dd) then
document.write "<td width=""50%"" bgcolor=""" & colour(dd) & """>" & depot(dd) & "</td>"
exit for
end if
next
document.write "<td></td>"
document.write "<td></td>"
document.write "<td></td>"
document.write "<td></td>"
document.write "<td></td>"
document.write "</tr>"
next
</script>
</table>
</script>
</body>
</html>
RE: Cell Colour based on Value
Like in all games, fighting the wizard is difficult. It is doable but you will fail many times. All the code is crammed on one line and there are no comments to help you. You have to see what it is doing, modify what it is going to generate and then it will do what you want.
Alternatively, instead of fighting the wizard, you could use a dsnless connection and do the code by yourself. Have a look at
http://www.haneng.com/lessons_20.asp. If you like stories, try http://ww
RE: Cell Colour based on Value
CODE
<head>
<% ' FP_ASP -- ASP Automatically generated by a FrontPage Component. Do not Edit.
FP_LCID = 2057 %>
<meta http-equiv="Content-Language" content="en-gb">
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<% ' FP_ASP -- ASP Automatically generated by a FrontPage Component. Do not Edit.
FP_CharSet = "windows-1252"
FP_CodePage = 1252 %>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Diary</title>
<meta name="Microsoft Border" content="b, default">
</head>
<body>
<p align="center">TesT New with Colour</p>
<table width="100%" border="1">
<thead>
<tr>
<th ALIGN="LEFT"><b>entryid</b></th>
<th ALIGN="LEFT"><b>depot</b></th>
<th ALIGN="LEFT"><b>customerid</b></th>
<th ALIGN="LEFT"><b>customername</b></th>
<th ALIGN="LEFT"><b>contact</b></th>
<th ALIGN="LEFT"><b>address1</b></th>
<th ALIGN="LEFT"><b>dateofhire</b></th>
<th ALIGN="LEFT"><b>destination</b></th>
<th ALIGN="LEFT"><b>date</b></th>
</tr>
</thead>
<tbody>
<!--webbot bot="DatabaseRegionStart" s-columnnames="entryid,depot,customerid,customername,contact,address1,address2,town,county,postcode,telephone,fax,dateofhire,destination,pickup1,pickup2,pickup3,starttime,returntime,typeofcoach,sizeofcoach,specials,price,drivername,regno,returndate,deposit,comments,km,invno,am,pm,date" s-columntypes="3,202,2,202,202,202,202,202,202,202,202,202,135,202,202,202,202,202,202,202,202,202,6,202,202,135,6,202,202,202,202,202,135" s-dataconnection="Diary" b-tableformat="TRUE" b-menuformat="FALSE" s-menuchoice s-menuvalue b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="FALSE" s-recordsource="Diary" s-displaycolumns="entryid,depot,customerid,customername,contact,address1,dateofhire,destination,date" s-criteria s-order s-sql="SELECT * FROM Diary" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields s-norecordsfound="No records returned." i-maxrecords="256" i-groupsize="0" botid="0" u-dblib="../_fpclass/fpdblib.inc" u-dbrgn1="../_fpclass/fpdbrgn1.inc" u-dbrgn2="../_fpclass/fpdbrgn2.inc" tag="TBODY" preview="<tr><td colspan=64 bgcolor="#FFFF00" width="100%"><font color="#000000">This is the start of a Database Results region. The page must be fetched from a web server with a web browser to display correctly; the current web is stored on your local disk or network.</font></td></tr>" startspan --><!--#include file="../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM Diary"
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=9 align=""LEFT"" width=""100%"">No records returned.</td></tr>"
fp_sDataConn="Diary"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&entryid=3&depot=202&customerid=2&customername=202&contact=202&address1=202&address2=202&town=202&county=202&postcode=202&telephone=202&fax=202&dateofhire=135&destination=202&pickup1=202&pickup2=202&pickup3=202&starttime=202&returntime=202&typeofcoach=202&sizeofcoach=202&specials=202&price=6&drivername=202®no=202&returndate=135&deposit=6&comments=202&km=202&invno=202&am=202&pm=202&date=135&"
fp_iDisplayCols=9
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="47516" --><tr>
<td>
<!--webbot bot="DatabaseResultColumn" s-columnnames="entryid,depot,customerid,customername,contact,address1,address2,town,county,postcode,telephone,fax,dateofhire,destination,pickup1,pickup2,pickup3,starttime,returntime,typeofcoach,sizeofcoach,specials,price,drivername,regno,returndate,deposit,comments,km,invno,am,pm,date" s-column="entryid" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>entryid<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"entryid")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="15463" --></td>
<td>
<!--webbot bot="DatabaseResultColumn" s-columnnames="entryid,depot,customerid,customername,contact,address1,address2,town,county,postcode,telephone,fax,dateofhire,destination,pickup1,pickup2,pickup3,starttime,returntime,typeofcoach,sizeofcoach,specials,price,drivername,regno,returndate,deposit,comments,km,invno,am,pm,date" s-column="depot" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>depot<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"depot")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="9347" --></td>
<td>
<!--webbot bot="DatabaseResultColumn" s-columnnames="entryid,depot,customerid,customername,contact,address1,address2,town,county,postcode,telephone,fax,dateofhire,destination,pickup1,pickup2,pickup3,starttime,returntime,typeofcoach,sizeofcoach,specials,price,drivername,regno,returndate,deposit,comments,km,invno,am,pm,date" s-column="customerid" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>customerid<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"customerid")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="31660" --></td>
<td>
<!--webbot bot="DatabaseResultColumn" s-columnnames="entryid,depot,customerid,customername,contact,address1,address2,town,county,postcode,telephone,fax,dateofhire,destination,pickup1,pickup2,pickup3,starttime,returntime,typeofcoach,sizeofcoach,specials,price,drivername,regno,returndate,deposit,comments,km,invno,am,pm,date" s-column="customername" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>customername<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"customername")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="34187" --></td>
<td>
<!--webbot bot="DatabaseResultColumn" s-columnnames="entryid,depot,customerid,customername,contact,address1,address2,town,county,postcode,telephone,fax,dateofhire,destination,pickup1,pickup2,pickup3,starttime,returntime,typeofcoach,sizeofcoach,specials,price,drivername,regno,returndate,deposit,comments,km,invno,am,pm,date" s-column="contact" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>contact<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"contact")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="15573" --></td>
<td>
<!--webbot bot="DatabaseResultColumn" s-columnnames="entryid,depot,customerid,customername,contact,address1,address2,town,county,postcode,telephone,fax,dateofhire,destination,pickup1,pickup2,pickup3,starttime,returntime,typeofcoach,sizeofcoach,specials,price,drivername,regno,returndate,deposit,comments,km,invno,am,pm,date" s-column="address1" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>address1<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"address1")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="14794" --></td>
<td>
<!--webbot bot="DatabaseResultColumn" s-columnnames="entryid,depot,customerid,customername,contact,address1,address2,town,county,postcode,telephone,fax,dateofhire,destination,pickup1,pickup2,pickup3,starttime,returntime,typeofcoach,sizeofcoach,specials,price,drivername,regno,returndate,deposit,comments,km,invno,am,pm,date" s-column="dateofhire" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>dateofhire<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"dateofhire")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="29543" --></td>
<td>
<!--webbot bot="DatabaseResultColumn" s-columnnames="entryid,depot,customerid,customername,contact,address1,address2,town,county,postcode,telephone,fax,dateofhire,destination,pickup1,pickup2,pickup3,starttime,returntime,typeofcoach,sizeofcoach,specials,price,drivername,regno,returndate,deposit,comments,km,invno,am,pm,date" s-column="destination" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>destination<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"destination")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="31938" --></td>
<td>
<!--webbot bot="DatabaseResultColumn" s-columnnames="entryid,depot,customerid,customername,contact,address1,address2,town,county,postcode,telephone,fax,dateofhire,destination,pickup1,pickup2,pickup3,starttime,returntime,typeofcoach,sizeofcoach,specials,price,drivername,regno,returndate,deposit,comments,km,invno,am,pm,date" s-column="date" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>date<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"date")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="5315" --></td>
</tr>
<!--webbot bot="DatabaseRegionEnd" b-tableformat="TRUE" b-menuformat="FALSE" u-dbrgn2="../_fpclass/fpdbrgn2.inc" i-groupsize="0" clientside tag="TBODY" preview="<tr><td colspan=64 bgcolor="#FFFF00" width="100%"><font color="#000000">This is the end of a Database Results region.</font></td></tr>" startspan --><!--#include file="../_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" endspan i-checksum="56926" --></tbody>
</table>
<p align="center">
</body>
</html>
RE: Cell Colour based on Value
CODE
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>Select using DSN</title>
</head>
<body>
<p align="center">TesT New with Colour</p>
<%
' Set up the lookup table
dim depot, colour
depot = array ("LEY", "WKS", "BOS", "ASP", "HWD")
colour = array("red", "blue", "green", "cyan", "magenta")
depotmin = lbound(depot)
depotmax = ubound(depot)
dim conn, mdbpath, connstr, sqlQuery, field, dvalue, dcolour, dd
connstr = "Diary"
sqlQuery = "select * from Diary"
' These are the fields we want to print
field = array ("entryid","depot", "customerid", "customername","contact","address1","dateofhire","destination","date")
set conn = Server.CreateObject("ADODB.Connection")
conn.Open connstr
set rs = conn.Execute (sqlQuery)
%>
<table width="100%" border="1">
<thead>
<tr>
<%
' Print the header
for each f in field
Response.Write("<th>" & f & "</th>")
next%>
</tr>
</thead>
<tbody>
<%
if rs.eof then
' No data
Response.Write ("<tr><td colspan=<" & UBound(field) & "align=left width=""100%"">No records returned.</td></tr>")
else
do while not rs.eof
Response.Write ("<tr>")
for each f in field
if f = "depot" then
' extract the value
dvalue = rs(f)
' find the matching colour
dcolour = depot(depotmin)
for dd = depotmin to depotmax
if dvalue = depot(dd) then
dcolour = colour(dd)
exit for
end if
next
Response.Write ("<td width=""50%"" bgcolor=""" & dcolour & """>" & dvalue & "</td>")
else
Response.Write ("<td>" & rs(f) & "</td>")
end if
next
Response.Write ("</tr>")
rs.MoveNext
loop
end if
%>
</tbody>
</body>
RE: Cell Colour based on Value
RE: Cell Colour based on Value
You could try the following in place of conn.open
CODE
connstr = "Driver={Microsoft Access Driver (*.mdb)};"
connstr = connstr & "DBQ=" & mdbpath & ";"
conn.Open connstr