I have an excel sheet where I have computed subtotals based on change in column C values and have summed up column H. This has generated a row with the text "Total" in column C for each value (example: "*Blue Total") and has the sub-total sum in column H.
I now want to highlight the row using conditional formatting if subtotal on column H is negative. What formula should I use in conditional formating.
I followed the following steps: a) select all rows including the header row b) created a new conditional formula and tried the formula =AND(ISNUMBER(SEARCH("Total",$C1)),$H1<0) but this seems to misfire and highlight incorrect rows. The first row is the header row.
I have also tried to limit the scope to the data rows by starting with row 2 as in =AND(ISNUMBER(SEARCH("Total",$C2)),$H2<0)
If the formatting is working, but on the wrong rows, your formula is probably referencing the wrong row. With conditional formatting you have to be careful about which cell is active, not just selected, when you apply the formatting.
The safest way is to just select one single cell, apply the conditional formatting, and then change the "Applies to" range after you are certain the formatting works.
Below example both use the same formula to reference the first row, but on the left side it was applied when the 1st row was active, on the right side it was applied when the 2nd row was active.