0

I have a spreadsheet where I want to search for the word "Total" in column headings, then look down the column that matched for "Total", find any row that has a value greater than 1 and highlight the row. The reason for searching for "Total" is that more columns may be added at any time but there will always be a "Total" column, and I will always want to highlight that row if the total is greater than 0.

I got very confused using the MATCH function.

I have a sample spreadsheet but can't see how to upload it.

Ram
  • 3,092
  • 10
  • 40
  • 56
JOLT
  • 1
  • 1
  • with or without VBA? – Klaster Jul 03 '15 at 09:45
  • You can try using this http://www.techonthenet.com/excel/formulas/cell.php to retrieve the column number which has "total" in it `=CELL("col", (function that gets the cell with "total" in it))` – Alexandru Cimpanu Jul 03 '15 at 09:56
  • As you have not included VBA as a tag I am assuming that this is a manual process that you perform. Is this correct? If you add the conditional formatting to the Total row, even if you add subsequent columns the conditional formatting, will still stay with the Total column. I don't see why a formula is required for this, unless I'm misunderstanding the question. – nbayly Jul 03 '15 at 18:06

1 Answers1

0

With a warning that I either misunderstood the requirement or now misunderstand it and assuming column headings are in Row1, please try selecting the entire sheet and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true:: 

=OFFSET($A1,,MATCH("Total",$1:$1,0)-1)>0

Format... with formatting of your choice, OK, OK.

pnuts
  • 58,317
  • 11
  • 87
  • 139