Conditional Formats using Conditional Fomula or another method

MsCrystal1

New Member
Joined
Jun 30, 2015
Messages
6
I'm stumped I have multiple rows and columns that are independent of each other. I need to setup a legend that's automated with three colors.

The spreadsheet is setup like this ...
ABCDEFGH
SLHWSDV
1Goal.2.3.3.4.6.6
2Actual.1.4.3.5.7.6
Total
4Goal.4.5.7.4.40.
5Actual.5.5.3.10.5.10
Total

<tbody>
</tbody>

Now the legend would need to function so that Goal/Actual for set the following colors
GreenMeeting 90% or more of 2014 goals
YellowAchieve 60% to 89% of 2014 goals
RedAchieve less than 60% of 2014 goals
GrayNo goal

<tbody>
</tbody>
So C2/C1 has to be evaluated against the above criteria
.2/.1=.5 which means it should be red I need the color to apply to C1 and C2
and then the same for D2/D1, E2/E1, F2/F1 and G2/G1 then move down to D5/D4, E5/E4

I hope this makes sense ... I have no clue where to start although it seems like this should be fairly easy to figure out.

Any assistance is appreciated.
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,048
Office Version
365
Platform
Windows
I think I can help you. Conditional Formatting can be a royal pain, so put on your seatbelt. In my example below, select cell B2.

In the ribbon, select Conditional Formatting|New Rule|Use formula...|type this into the Format values box =B2=0 |Format, Fill, select a grey|OK.

In the ribbon, select Conditional Formatting|New Rule|Use formula...|type this into the Format values box =AND(B3/B2>0,B3/B2<0.6) |Format, Fill, select a red|OK.

In the ribbon, select Conditional Formatting|New Rule|Use formula...|type this into the Format values box =AND(B3/B2>=0.6,B3/B2<0.9) |Format, Fill, select a yellow|OK.

In the ribbon, select Conditional Formatting|New Rule|Use formula...|type this into the Format values box =B3/B2>=0.9 |Format, Fill, select a green|OK.

Then go to cell B3 and repeat verbatim the above four steps for CondFrmt.

Select cells B2 and B3, right-click copy, select cells C2 to G3 and right-click Paste Formatting. Select cells B6 to G7 and right-click Paste Formatting. Select other cells in any subsequent rows that are structured identically to our A1 to G3 and right-click Paste Formatting.

[Notice I included in A9 to A12 text of the formatting's logical tests so you can copy and paste them instead of typing them all, should you choose to do so.]

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">S</td><td style=";">L</td><td style=";">H</td><td style=";">W</td><td style=";">SD</td><td style=";">V</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Goal</td><td style="text-align: right;;">0.2</td><td style="text-align: right;;">0.3</td><td style="text-align: right;;">0.3</td><td style="text-align: right;;">0.4</td><td style="text-align: right;;">0.6</td><td style="text-align: right;;">0.6</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Actual</td><td style="text-align: right;;">0.1</td><td style="text-align: right;;">0.4</td><td style="text-align: right;;">0.3</td><td style="text-align: right;;">0.5</td><td style="text-align: right;;">0.7</td><td style="text-align: right;;">0.6</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Total</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Goal</td><td style="text-align: right;;">0.4</td><td style="text-align: right;;">0.5</td><td style="text-align: right;;">0.7</td><td style="text-align: right;;">0.4</td><td style="text-align: right;;">0.4</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Actual</td><td style="text-align: right;;">0.5</td><td style="text-align: right;;">0.5</td><td style="text-align: right;;">0.3</td><td style="text-align: right;;">0.1</td><td style="text-align: right;;">0.5</td><td style="text-align: right;;">0.1</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">=B2=0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">=AND(B3/B2>0,B3/B2<0.6)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">=AND(B3/B2>=0.6,B3/B2<0.9)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">=B3/B2>=0.9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet56</p><br /><br />
 
Last edited:

MsCrystal1

New Member
Joined
Jun 30, 2015
Messages
6
Thanks for this I have to play with the relative references because it's not picking up the correct field when I copy it over.
 

MsCrystal1

New Member
Joined
Jun 30, 2015
Messages
6
I think I can help you. Conditional Formatting can be a royal pain, so put on your seatbelt. In my example below, select cell B2.

In the ribbon, select Conditional Formatting|New Rule|Use formula...|type this into the Format values box =B2=0 |Format, Fill, select a grey|OK.

In the ribbon, select Conditional Formatting|New Rule|Use formula...|type this into the Format values box =AND(B3/B2>0,B3/B2<0.6) |Format, Fill, select a red|OK.

In the ribbon, select Conditional Formatting|New Rule|Use formula...|type this into the Format values box =AND(B3/B2>=0.6,B3/B2<0.9) |Format, Fill, select a yellow|OK.

In the ribbon, select Conditional Formatting|New Rule|Use formula...|type this into the Format values box =B3/B2>=0.9 |Format, Fill, select a green|OK.

Then go to cell B3 and repeat verbatim the above four steps for CondFrmt.

Select cells B2 and B3, right-click copy, select cells C2 to G3 and right-click Paste Formatting. Select cells B6 to G7 and right-click Paste Formatting. Select other cells in any subsequent rows that are structured identically to our A1 to G3 and right-click Paste Formatting.





[Notice I included in A9 to A12 text of the formatting's logical tests so you can copy and paste them instead of typing them all, should you choose to do so.]

Excel 2012
ABCDEFG
1SLHWSDV
2Goal0.20.30.30.40.60.6
3Actual0.10.40.30.50.70.6
4Total
5
6Goal0.40.50.70.40.40
7Actual0.50.50.30.10.50.1
8
9=B2=0
10=AND(B3/B2>0,B3/B2<0.6)
11=AND(B3/B2>=0.6,B3/B2<0.9)
12=B3/B2>=0.9

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
b3/b2
Sheet56
I'm not able to copy the formula to the other cells it doesn't copy correctly.

Essentially I need b3/b2,c3/c2,d3/d2,e3/e2 then change the color of both b3 and b2. B7/b6,c7/c6,d7/d6,e7/e6 change the color for row 6 and 7 and repeat for each grouping
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,048
Office Version
365
Platform
Windows
I don't see any problem with the above procedure. Perhaps if you try it again in a blank workbook we can get to the bottom of it.
 

MsCrystal1

New Member
Joined
Jun 30, 2015
Messages
6
It's not changing row 3. I know I need to apply the conditional formatting to both row but then when I copy it changes the references.

Goal0.20.30.30.40.60.6
Actual0.10.40.30.50.70.6
Total REDGreen Green GreenGreen Green
Goal0.40.50.70.40.40
Actual0.50.50.30.10.50.1
Green Green Red Red Green Gray

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>
In my actual table it's dividing by the total. Which is below the actual. I need to know how to make sure it continues to pick up the result of B2/B3 for both B2 and B3 and then C3/C2 for C2 and C3 so on and so forth
 

MsCrystal1

New Member
Joined
Jun 30, 2015
Messages
6
Okay I fixed that issue ... my issue is now trying to figure out how to incorporate a rule for when the denominator is zero and the result yields an error. Presently it just remains white but I need it to be red so ... for example is if B3=0 as opposed to .1 which would yield an #div/0
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,048
Office Version
365
Platform
Windows
You're all over the place here! Row 2 is the denominator and when it is 0 the colour is grey; changing B3 to 0 would not create an error since O is the numerator.
 

Forum statistics

Threads
1,081,976
Messages
5,362,511
Members
400,678
Latest member
alecalec202

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top