INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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.

RE: Cell Colour based on Value

It will be something like this

CODE

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

If this doesn't fix your problem I will have to setup a database to see what sort of code the wizard is generating.  It could be that you are putting in code where the wizard expects to write code and it is clobbering it.  I find that spinning my own database code is faster than using the wizard.

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://www.powerasp.net/content/database/dsn_vs_dnsless.asp.  
 
 

RE: Cell Colour based on Value

(OP)
Hi, here is my code, can you try and add yours to it to make it work?

CODE

<html>

<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="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot; width=&quot;100%&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;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.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;" 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&regno=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="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;entryid&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" 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="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;depot&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" 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="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;customerid&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" 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="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;customername&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" 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="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;contact&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" 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="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;address1&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" 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="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;dateofhire&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" 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="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;destination&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" 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="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;date&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" 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="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot; width=&quot;100%&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the end of a Database Results region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;" 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

It is easier to rewrite everyting without using the wizard.  Wizard fighting is best left to the masochists.  It will look something like this

CODE

<html>

<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

(OP)
That just comes up with an ODBC error, is there not a way to format the wizard generated table using Javascript? If 'depot' = LEY then colour = blue etc ???

RE: Cell Colour based on Value

You used to be able to do that before they added in their checksums.  The checksums work out whether you have modified the code or not and just go bezerk.

You could try the following in place of conn.open

CODE

mdbpath = Server.MapPath("Diary")
connstr = "Driver={Microsoft Access Driver (*.mdb)};"
connstr = connstr & "DBQ=" & mdbpath & ";"
conn.Open connstr
I am assuming you are using an Access DB.  The connection string is different for different database engines.
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close