Conditional Formatting of Previous Row , Based on cell value

[balaji3081]

Hi All,

Is it possible to format (Underline rows) previous rows based on cell values in a column using Conditional Formatting.



A B C C D
1 Mango 5.7 11.5 10.5 3.4
2 Banana 0.3 0.3 0.3 3.4
3 Apple 0.7 0.7 3.7 3.4
4 Fruits 6.7 12.5 14.5 10.1
5 Lion 1.3 4.4 7.1 3.4
6 Elephant 5.7 11.5 10.5 3.4
7 Rhino 17.2 21.9 13.8 3.4
8 Animal 24.2 37.8 31.4 10.1
9 Ohio 74.8 53.0 20.6 3.4
10 Texas 127.8 73.5 24.0 3.4
11 Florida 201.3 97.5 27.3 3.4
12 States 404.0 224.0 71.9 10.1


I will upload a file soon, the requirement is to underline line 3,7,11 ( From Column B Through D) if value in column A is either - Fruits, Animal, States (in my actual file the order changes as per the drop down selections made ).

Also, I need rows 4, 8, 12 ( From Column A through D) to be bold if value in column A is either - Fruits, Animal, States.

Any help Appreciated.

 

[Luke M]

Make a list somewhere of the criteria. Let's say your list of things you want to act as triggers are in Z1:Z3. Setup the first CF, underline format, with rule of:
=COUNTIF($Z$1:$Z$3,$A2)

Setup 2nd rule with bold using similar formula.
=COUNTIF($Z$1:$Z$3,$A1)

 

[balaji3081]

Make a list somewhere of the criteria. Let's say your list of things you want to act as triggers are in Z1:Z3. Setup the first CF, underline format, with rule of:
=COUNTIF($Z$1:$Z$3,$A2)

Setup 2nd rule with bold using similar formula.
=COUNTIF($Z$1:$Z$3,$A1)

Awesome, Thanks a ton, I will apply to my file.

 

[balaji3081]

I see that the underline condition is only to the list of the number in the cell, is there a way to extend it to the complete cell width?

 

[balaji3081]

I got the solution to that by using Accounting Format.

 

[balaji3081]

I have a further question, Is it possible to align the subtotals as left and the rest with one space in the beginning.

 

[Luke M]

I don't think so. However, if you're putting in subtotals, which implies having formulas, we may be going about this wrong. Perhaps it would be better to just have the raw data (w/o subtotals), and then build a PivotTable? That would let you easily do the summations you want, and you could more easily format the different sections.