Conditional Formating Misfires

5 viewsconditional formattingexcel
0

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)