Excel conditional formatting question

[kranky]

I need to apply conditional formatting to cells when:
- Column B contains the word "total" AND
- the cell value is greater than 160

I thought I could do this by using AutoFilter to only display the rows with the word "total" in column B, then select the range of visible cells and apply the conditional formatting for cells > 160. This didn't work because Excel also applied the formatting to all the cells in the rows that were hidden by the filter. Wasn't expecting that - I thought it would only be applied to the visible cells.

The spreadsheet has too many rows (over 2000) to do this manually for every row that contains a total in Column B.

Any ideas?

 

[armstrda]

why not use the conditional formatting tool? Is this excel 2003 or 2007?

 

[kranky]

I'm trying to use conditional formatting (Excel 2003).

The difficulty is that it should only be applied to cells in certain rows, and I can't figure out how to isolate the conditional formatting to only those rows. I thought if I only displayed the rows I wanted using AutoFilter, I could select the range and apply it. But Excel applied the conditional formatting to the hidden rows also, which is no good.

 

[abaez]

Can you sort to get the cells with total in them together then use conditional formatting on those blocks of cells?

 

[kranky]

I tried that two different ways.

First I used AutoFilter to only display the rows with "total", then grabbed the range and applied conditional formatting. But it also carried through to the cells in the rows which weren't displayed that were in the range.

Second thing I tried was using AutoFilter, then highlighted the rows by selecting the row numbers, but got the same result after applying conditional formatting.

 

[armstrda]

I'm confused, so what is in column B? You have things that look like this?

A B
blah total 150
blah total 170

etc? And you want it to conditionally highlight column B if it says total and a number larger than 160?


If so you want to do a conditional format such that it formats the cell if the Cell Value is >= Total 160. I'm not sure exactly how you input this in excel 03 (it's been too long since I've used it as I'm on 2007 now)

 

[kranky]

Sorry my explanation is confusing, I'll try to explain further.

Column B has a list of names. Columns C through Q have numbers. When the name in Column B changes, there is a row showing the totals for Columns C through Q. When any of the numbers in Columns C through Q are > 160, I want the number to be red.

I have no trouble defining the conditional formatting. I can't seem to figure out how to apply it to ONLY the total lines (= contain the word "total" in Column B) I can use AutoFilter to display only those lines, but when I apply the formatting it ends up being applied to ALL rows, not just the ones with the word "total" in Column B.

(tried to add an example using Attach Code but couldn't get the line breaks to work)

 

[NeoV]

how about something like this?

=if(left(a3,5)="Total","Y","N")

insert a column with that formula in it - (A3 is only a placeholder, if you are in row 5 then it would be A5, etc)

then, in your conditional formatting, add a criteria that says if b3 = "Y" AND then your other conditional formatting for the colors - all the non-total rows will fail the first criteria.

 

[armstrda]

Again, just use a conditional format for columns C through Q. Have them like this:

color red if (B!x contains "total" && Cx > 160).

In the condtional format, there should be a choice for cell contains, use that, and the and, I can't remember in 2003 if it has the dual part, or if you actually have to type the equation.

 

[kranky]

Got it to work! Had to use the "formula is" style of conditional formatting. Couldn't use what NeoV suggested since I couldn't stick in an extra column (have to distribute the spreadsheet to executives).

Funny, I had looked at the "formula is" thing earlier, but I didn't understand that it would magically modify the cell references for all the cells in the selection so I didn't pursue it.

How did I get into this? I did a mockup for the executives and manually formatted everything just to show what the end product would look like. I (stupidly!) assumed I would have no trouble building it to work without manual formatting.

Thanks to everyone for the help, especially armstrda who got me pointed back at using a formula!