Conditional formatting for ranges that change

[BustedAvi]

Hello,

I would like to know how to define conditional formatting based on a lookup table for an unknown range. My situation is as follows:

I have a lookup table that looks like this:

<style type="text/css">
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</style>

<table class="tableizer-table">
<tr class="tableizer-firstrow"><th>Arsenic</th><th>50</th></tr> <tr><td>Chloride</td><td>250</td></tr> <tr><td>Conductivity</td><td>1500</td></tr> <tr><td>Fluoride</td><td>1.5</td></tr> <tr><td>Iron</td><td>0.3</td></tr> <tr><td>Lead</td><td>0.01</td></tr> <tr><td>Manganese</td><td>0.4</td></tr> <tr><td>Nitrate</td><td>50</td></tr> <tr><td>Nitrite</td><td>50</td></tr> <tr><td>Sulfate</td><td>250</td></tr> <tr><td>E. Coli</td><td>0</td></tr> <tr><td>Total Coliforms</td><td>0</td></tr> <tr><td>Total Hardness</td><td>300</td></tr> <tr><td>Turbidity</td><td>1</td></tr></table>


I have a template like this:


<style type="text/css">
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</style>

<table class="tableizer-table">
<tr class="tableizer-firstrow"><th>Arsenic</th><th>1</th><th>99</th></tr> <tr><td>Chloride</td><td>3</td><td>77</td></tr> <tr><td>Conductivity</td><td>5</td><td>3</td></tr> <tr><td>Copper</td><td>6</td><td>2</td></tr> <tr><td>Fluoride</td><td>7</td><td>9</td></tr> <tr><td>Iron</td><td>867</td><td>0</td></tr> <tr><td>Lead</td><td>4</td><td>9</td></tr> <tr><td>Manganese</td><td>45</td><td>6</td></tr> <tr><td>Nitrate</td><td>65</td><td>7</td></tr> <tr><td>Nitrite</td><td>45</td><td>45</td></tr> <tr><td>pH</td><td>7</td><td>23</td></tr> <tr><td>Phosphate</td><td>8</td><td>6</td></tr> <tr><td>Sulfate</td><td>45</td><td>3</td></tr> <tr><td>E. Coli</td><td>7</td><td>67</td></tr> <tr><td>Total Coliforms</td><td>8</td><td>9</td></tr> <tr><td>Total Hardness</td><td>98</td><td>9</td></tr> <tr><td>Turbidity</td><td>7</td><td>9</td></tr></table>


I need to apply conditional formatting to the template so that cells with a value that are equal or larger than the lookup table according to their row are highlighted.

The catch first is that the template does not have a defined range. My current code will remove rows if there is data missing (ex: if no data is in the cells next to arsenic, then that row is removed).

The second catch is that the report is set up so that a single page can only accommodate 8 columns of data. If more than 8 columns are needed, the template is restarted on the next column (shown below)


<style type="text/css">
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</style>

<table class="tableizer-table">
<tr class="tableizer-firstrow"><th>Arsenic</th><th>50</th><th>ppb</th><th>1</th><th>1</th><th>1</th><th>1</th><th>1</th><th>1</th><th>1</th><th>1</th><th>Arsenic</th><th>50</th><th>ppb</th><th>1</th><th>1</th><th>1</th><th>1</th><th>1</th><th>1</th><th>1</th><th>1</th></tr> <tr><td>Chloride</td><td>250</td><td>mg/L</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>Chloride</td><td>250</td><td>mg/L</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td></tr> <tr><td>Conductivity</td><td>1500</td><td>µS/cm</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>Conductivity</td><td>1500</td><td>µS/cm</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td></tr> <tr><td>Copper</td><td>1</td><td>mg/L</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>Copper</td><td>1</td><td>mg/L</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td></tr> <tr><td>Fluoride</td><td>1.5</td><td>mg/L</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>Fluoride</td><td>1.5</td><td>mg/L</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td></tr> <tr><td>Iron</td><td>0.3</td><td>mg/L</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>Iron</td><td>0.3</td><td>mg/L</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td></tr> <tr><td>Lead</td><td>0.01</td><td>mg/L</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>Lead</td><td>0.01</td><td>mg/L</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td></tr> <tr><td>Manganese</td><td>0.1</td><td>mg/L</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>Manganese</td><td>0.1</td><td>mg/L</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td></tr> <tr><td>Nitrate</td><td>50</td><td>mg/L</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>Nitrate</td><td>50</td><td>mg/L</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td></tr> <tr><td>Nitrite</td><td>3</td><td>mg/L</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>Nitrite</td><td>3</td><td>mg/L</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td></tr> <tr><td>pH</td><td>6.5 - 8.5</td><td>-</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>pH</td><td>6.5 - 8.5</td><td>-</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td></tr> <tr><td>Phosphate</td><td>-</td><td>mg/L</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>Phosphate</td><td>-</td><td>mg/L</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td></tr> <tr><td>Sulfate</td><td>250</td><td>mg/L</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>Sulfate</td><td>250</td><td>mg/L</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td></tr> <tr><td>E. Coli</td><td>0</td><td>cfu/100 mL</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>E. Coli</td><td>0</td><td>cfu/100 mL</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td></tr> <tr><td>Total Coliforms</td><td>0</td><td>cfu/100 mL</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>Total Coliforms</td><td>0</td><td>cfu/100 mL</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td></tr> <tr><td>Total Hardness</td><td>300</td><td>mg/L CaCO3</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>Total Hardness</td><td>300</td><td>mg/L CaCO3</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td></tr> <tr><td>Turbidity</td><td>5</td><td>NTU</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>Turbidity</td><td>5</td><td>NTU</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td></tr></table>





I am learning and would like to know how I would manage this?

I appreciate your help.

 

[tinyskills13]

As far as the 1st catch goes, can you apply the conditional formatting before removing the rows?

 

[BustedAvi]

That was my first plan of action, but if I set conditional formatting for a static range (say D4:K4), then I delete a row above it , the formatting is shifted to the wrong row.

 

[BustedAvi]

So any clue how to define conditional formatting for a range that can vary?

 

[BustedAvi]

Anyone?