Help needed with conditional formatting results

N

NeilF

Board Regular
Joined
Sep 4, 2007
Messages
100
I am trying to set up a conditional formatting query to highlight cells in my "actual" column, and I am getting some wierd results. I want cells where actual is equal to or greater than target, to be highlighted in green, and cells where actual is less than target to be highlighted in red.

I have used HTML maker to generate the following preview, but unfortunately the colour highlighting hasn't come out.

In my Excel 2010 worksheet, cell I7 is highlighted green; cells I8 to I17 inclusive are highlighted red (so far, so good), but puzzlingly, and incorrectly, cell I18 is highlighted in green, whereas it should be highlighted in red.



<b>Excel 2010</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 /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Monthly</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Actual</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Target</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">Mar-12</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">Apr-12</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">May-12</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">Jun-12</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">Jul-12</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">Aug-12</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">Sep-12</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">Oct-12</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">Nov-12</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">Dec-12</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">Jan-13</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">Feb-13</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">Total</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">6</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">24</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:3.6em;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">Visits</p><br /><br />

I generated the conditional format, by setting up a rule in cell I7, and then using format painter to apply it to cells I8 to I18 inclusive.

If I click on cell I18 and then on the conditional formatting button and then on "manage rules", I get the "Conditional formatting rules manager" dialogue box, which has 2 rules showing. (The identical dialogue box appears if clicked on any of the cells I8 to I18).

Rule one reads:-
Cell value >=J9 Green highlight Applies to =$I$8:$I$18

Rule two reads:-
Cell value "less than symbol"J9 Red highlight Applies to =$I$8:$I$18

(For some strange reason, if I put the actual less than symbol in the line above, half the text doesn't appear when I preview the post...?!)

Can anyone explain why cell I18 is highlighted green (it should be red, as 1 is less than 2).

Can anyone help me to get the conditional syntax right?

Thanks

Neil
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
NeilF said:
I am trying to set up a conditional formatting query to highlight cells in my "actual" column, and I am getting some wierd results. I want cells where actual is equal to or greater than target, to be highlighted in green, and cells where actual is less than target to be highlighted in red.

I have used HTML maker to generate the following preview, but unfortunately the colour highlighting hasn't come out.

In my Excel 2010 worksheet, cell I7 is highlighted green; cells I8 to I17 inclusive are highlighted red (so far, so good), but puzzlingly, and incorrectly, cell I18 is highlighted in green, whereas it should be highlighted in red.



Excel 2010<TABLE style="BORDER-RIGHT: #bbb 1px solid; BORDER-TOP: #bbb 1px solid; BORDER-LEFT: #bbb 1px solid; BORDER-BOTTOM: #bbb 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #dae7f5; TEXT-ALIGN: center"><TH></TH><TH>H</TH><TH>I</TH><TH>J</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Monthly</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Actual</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Target</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: center"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: right">Mar-12</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: center">2</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: center">2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: right">Apr-12</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: center">2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: right">May-12</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: center">2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: right">Jun-12</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: center">2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">11</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: right">Jul-12</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: center">2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">12</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: right">Aug-12</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: center">2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">13</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: right">Sep-12</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: center">2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">14</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: right">Oct-12</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: center">2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">15</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: right">Nov-12</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: center">2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">16</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: right">Dec-12</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: center">2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">17</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: right">Jan-13</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: center">2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">18</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: right">Feb-13</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: center">2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">19</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: center"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">20</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: right">Total</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: center">6</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: center">24</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">21</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>
Visits​




I generated the conditional format, by setting up a rule in cell I7, and then using format painter to apply it to cells I8 to I18 inclusive.

If I click on cell I18 and then on the conditional formatting button and then on "manage rules", I get the "Conditional formatting rules manager" dialogue box, which has 2 rules showing. (The identical dialogue box appears if clicked on any of the cells I8 to I18).

Rule one reads:-
Cell value >=J9 Green highlight Applies to =$I$8:$I$18

Rule two reads:-
Cell value "less than symbol"J9 Red highlight Applies to =$I$8:$I$18

(For some strange reason, if I put the actual less than symbol in the line above, half the text doesn't appear when I preview the post...?!)

Can anyone explain why cell I18 is highlighted green (it should be red, as 1 is less than 2).

Can anyone help me to get the conditional syntax right?

Thanks

Neil
Click to expand...
I don't have Excel 2010 but it works OK for me in Excel 2007 if...

I use the "use a formula..." option to apply the formatting.

I never use the predefined rules. I always use the formula option. Also, I never use the format painter. I always apply the formatting at once.

Select the *entire* range I7:I18 starting from cell I7.
Cell I7 will be the active cell. The active cell is the
one cell in the selected range that is not shaded. The
formula will be relative to the active cell.

Conditional Formatting
Use a formula to determine which cells to format
Enter this formula in the box below:
=I7>=J7
Set the fill color to green
OK>OK

New Rule

Use a formula to determine which cells to format
Enter this formula in the box below:
=I7<J7< p> < J7
Set the fill color to red
OK out
 
Upvote 0
Thanks for that Biff. I have now got it to work, using your approach.

One further question. You say you never use format painter, but from what I am observing, it seems to work okay.

Anything I should be wary of if using format painter to replicate a range of conditional formatting formulas...?

Thanks

Neil
 
Upvote 0
NeilF said:
Thanks for that Biff. I have now got it to work, using your approach.
Click to expand...
Good deal!

One further question. You say you never use format painter, but from what I am observing, it seems to work okay.

Anything I should be wary of if using format painter to replicate a range of conditional formatting formulas...?

Thanks

Neil
Click to expand...
I guess it's just a personal preference.

I've literally never used the format painter so if there are things to look out for I wouldn't know what they are! :eeek:

When a range needs to be formatted I format it all at the same time.

If I ever need to copy the formatting I use:

Select a cell with the formatting
Right click>Copy
Select the cells to format
Righ click>Paste special>Formats>OK
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
554
smozgur
smozgur
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
494
VBAEXCELNew
V
E
  • Question Question
VBA code clean up
Replies
9
Views
511
mumps
mumps
T
  • Solved
Looking for VBA Code to Conditionally Format Time Tracker on Entry
Replies
3
Views
183
Tara_A_Data
T
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G

Forum statistics

Threads
1,223,714
Messages
6,174,052
Members
452,542
Latest member
Bricklin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top