Change color if less than or greater than

[Xirom431]

Hi,

I have this spreadsheet. I would like to have the % Completion column change to red letter if it is less than Target column and green if it is equal or greater than Target. Thanks and I use Office 2010.

<TABLE style="WIDTH: 215pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=286><COLGROUP><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" span=3 width=61><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 46pt; HEIGHT: 15.75pt; BORDER-TOP: red 0.5pt solid; BORDER-RIGHT: red 0.5pt solid" class=xl72 height=21 width=61>Target</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: red 0.5pt solid; BORDER-RIGHT: red 0.5pt solid" class=xl72 width=61>Actual</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: red 0.5pt solid; BORDER-RIGHT: red 0.5pt solid" class=xl72 width=61>Planned</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; WIDTH: 77pt; BORDER-TOP: red 0.5pt solid; BORDER-RIGHT: red 0.5pt solid" class=xl72 width=103>% Completion</TD></TR><TR style="HEIGHT: 17.1pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.1pt; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73 height=22>90</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73>63</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73>75</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl74>84.0%</TD></TR><TR style="HEIGHT: 17.1pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.1pt; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73 height=22>90</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73>62</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73>75</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl74>82.7%</TD></TR><TR style="HEIGHT: 17.1pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.1pt; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73 height=22>90</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73>71</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73>75</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl74>94.7%</TD></TR><TR style="HEIGHT: 17.1pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.1pt; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73 height=22>90</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73>50</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73>75</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl74>66.7%</TD></TR><TR style="HEIGHT: 17.1pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.1pt; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73 height=22>90</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73>63</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73>75</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl74>84.0%</TD></TR><TR style="HEIGHT: 17.1pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.1pt; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73 height=22>90</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73>32</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73>75</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl74>42.7%</TD></TR><TR style="HEIGHT: 17.1pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.1pt; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73 height=22>90</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73>55</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73>75</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl74>73.3%</TD></TR><TR style="HEIGHT: 17.1pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.1pt; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73 height=22>90</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73>45</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73>75</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl74>60.0%</TD></TR><TR style="HEIGHT: 17.1pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.1pt; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73 height=22>90</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73>60</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73>75</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl74>80.0%</TD></TR><TR style="HEIGHT: 17.1pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.1pt; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73 height=22>90</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73>75</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73>75</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl74>100.0%</TD></TR><TR style="HEIGHT: 17.1pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.1pt; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73 height=22>90</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73>21</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73>75</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl74>28.0%</TD></TR><TR style="HEIGHT: 17.1pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.1pt; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73 height=22>90</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73>31</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl73>75</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl74>41.3%</TD></TR></TBODY></TABLE>

 

[excel_vba_guru]

Should be able to use conditional formatting.

Go to Conditional Formatting, select new rule, then select Use a formula to determine which cells to format.

Given your data above, and that data starts in A1, apply this formatting formula to column to column D.

Green: =if($D2>=$A2/100)
Red: =if($D2<$A2/100)

Best of luck!
Excel VBA Guru

 

[Brian F]

Conditional formating, use a formula

for green the formula is "=$A2<=$D2" over the range =$D$2:$D$12
for red it is "=$A2>$D2" over the same range