Find percentage difference between two numbers and according add column

D

divyaquamara

Board Regular
Joined
Jun 27, 2011
Messages
67
Hi,
I have data in which column
AB AC ADhave following data
AB AC AD
<TABLE style="WIDTH: 177pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=236 border=0 x:str><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=83 height=17>YTD Budget</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 67pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=89>YTD Actuals</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64 x:str="Status ">Status </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>5</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>4</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>3</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>5</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>4</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>7</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>6</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>4</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>7</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>9</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>
We have to use the formula :
[ |AB-AC|/AC ] *100
If this gives value less than or equal to 10% we insert Green in column AD.
If this gives value greater Than 10% and less than or equal to 20 % we insert Amber in column AD.
If this gives value greater than 20% we insert Red in column AD.
I have to write a macro to do this.
Please do guide.
Thanks in advance.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
You could do this with the Conditional Formatting feature. You wouldn't need a macro.

Condition 1
Formula is =ABS(AB2-AC2)/AC2*100>20
Background color: Red

Condition 2
Formula is =ABS(AB2-AC2)/AC2*100>10
Background color: Amber

Condition 3
Formula is =ABS(AB2-AC2)/AC2*100>0
Background color: Green
 
Upvote 0
Hi,
I want to write a macro since I need to automate the process.
Also I think I have not explined the question well first time.
In the Status Column I need the word Green, Red or Amber put in rather than the color itself.
Can you guide me on hoe I should proceed.
Thanks in advance
 
Upvote 0
Or try this (without macro):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">YTD Budget</td><td style="font-weight: bold;text-align: center;;">YTD Actuals</td><td style="font-weight: bold;text-align: center;;">Status </td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">3.6</td><td style="text-align: center;;">4</td><td style="text-align: center;;">Green</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">4</td><td style="text-align: center;;">5</td><td style="text-align: center;;">Amber</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">6</td><td style="text-align: center;;">7</td><td style="text-align: center;;">Amber</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">6</td><td style="text-align: center;;">4</td><td style="text-align: center;;">Red</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">7</td><td style="text-align: center;;">9</td><td style="text-align: center;;">Red</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">100</td><td style="text-align: center;;">100</td><td style="text-align: center;;">Green</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">90</td><td style="text-align: center;;">100</td><td style="text-align: center;;">Green</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">89</td><td style="text-align: center;;">100</td><td style="text-align: center;;">Amber</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">80</td><td style="text-align: center;;">100</td><td style="text-align: center;;">Amber</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">79</td><td style="text-align: center;;">100</td><td style="text-align: center;;">Red</td></tr></tbody></table><p style="width:3em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Plan2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=LOOKUP(<font color="Blue">ABS(<font color="Red">A2-B2</font>)/B2,{0,"Green";0.101,"Amber";0.201,"Red"}</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz
 
Upvote 0
Hi Markmzz,
Thanks a lot. This works perfectly.
But I forgot to put in one condition by that I mean there are some rows that have no data, so there should not be any thing for those.
And I need to put this in a macro since I need to automate it for a large amout of data.
Can you guide me how I should achieve this.
Thanks for your help.

<TABLE style="WIDTH: 336pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=448 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=7 width=64><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD class=xl24 id=td_post_2793594 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=64 height=34>Sr.no</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Project No.</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64 x:str="Lead ">Lead </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Asst Lead</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>YTD Budget

</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>YTD Actuals</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>Status</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">A1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">xx</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">xx</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>3.6</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>4</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>Green</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>2</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">B1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">xx</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">xx</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>3</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">C1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">xx</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">xx</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>6</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>7</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>Amber</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>4</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">D1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">xx</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">xx</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>5</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">E1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">xx</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">xx</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>7</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>9</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>Red</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>6</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">F1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">xx</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">xx</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>100</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>100</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>Green</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>7</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">G1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">xx</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">xx</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>90</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>100</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>Green</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>8</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">H1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">xx</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">xx</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>9</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">I1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">xx</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">xx</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>80</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>100</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>Amber</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>10</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">J1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">xx</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">xx</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>79</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>100</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>Red

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>
 
Upvote 0
If I understand what you want (for yours examples), then I think that you don't need a macro for this.

Look at this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">YTD Budget</td><td style="font-weight: bold;text-align: center;;">YTD Actuals</td><td style="font-weight: bold;text-align: center;;">Status </td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">3.6</td><td style="text-align: center;;">4</td><td style="text-align: center;;">Green</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">4</td><td style="text-align: center;;">5</td><td style="text-align: center;;">Amber</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">6</td><td style="text-align: center;;">4</td><td style="text-align: center;;">Red</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">100</td><td style="text-align: center;;">100</td><td style="text-align: center;;">Green</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">90</td><td style="text-align: center;;">100</td><td style="text-align: center;;">Green</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">89</td><td style="text-align: center;;">100</td><td style="text-align: center;;">Amber</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">79</td><td style="text-align: center;;">100</td><td style="text-align: center;;">Red</td></tr></tbody></table><p style="width:3em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Plan2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=IF(<font color="Blue">A2="","",LOOKUP(<font color="Red">ABS(<font color="Green">A2-B2</font>)/B2,{0,"Green";0.101,"Amber";0.201,"Red"}</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

If I'm wrong, then give me more examples.

Use the link below for helper to give me more examples.

http://www.mrexcel.com/forum/showpost.php?p=2545970&postcount=2

Markmzz
 
Upvote 0
Thanks a lot. This solves my earlier problem.

The only reason I want to put this in a macro is because it contains a large amount of data and I dont want to type in the formula and acroll it down to all the cells.
(This needs to run on a click since it has to run by other people as well.)
If there is another way than a macro I am open to ur suggestion.
Thanks in advance.
 
Upvote 0
divyaquamara said:
Thanks a lot. This solves my earlier problem.

The only reason I want to put this in a macro is because it contains a large amount of data and I dont want to type in the formula and acroll it down to all the cells.
(This needs to run on a click since it has to run by other people as well.)
If there is another way than a macro I am open to ur suggestion.
Thanks in advance.
Click to expand...

Try this:

Note: I used the layout of your last example (without tha column Status) and the sheet with the name Budget.

Code: 
Sub CreateStatus()
   Application.ScreenUpdating = False
    Sheets("Budget").Select
    
    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    Cells(1, LastCol + 1).Value = "Status"
    myFormula = "=IF(E2="""","""",LOOKUP(ABS(E2-F2)/F2,{0,""Green"";0.101,""Amber"";0.201,""Red""}))"
    Range(Cells(2, LastCol + 1), Cells(LastRow, LastCol + 1)).Formula = myFormula
    
    Cells(1, LastCol + 1).EntireColumn.AutoFit
    
   Application.ScreenUpdating = True
End Sub

Result

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;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="font-weight: bold;text-align: center;;">Sr.no</td><td style="font-weight: bold;text-align: center;;">Project No.</td><td style="font-weight: bold;text-align: center;;">Lead </td><td style="font-weight: bold;text-align: center;;">Asst Lead</td><td style="font-weight: bold;text-align: center;;">YTD Budget</td><td style="font-weight: bold;text-align: center;;">YTD Actuals</td><td style="font-weight: bold;;">Status</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">A1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">3.6</td><td style="text-align: center;;">4</td><td style=";">Green</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">2</td><td style="text-align: center;;">B1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">3</td><td style="text-align: center;;">C1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">6</td><td style="text-align: center;;">7</td><td style=";">Amber</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">4</td><td style="text-align: center;;">D1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">5</td><td style="text-align: center;;">E1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">7</td><td style="text-align: center;;">9</td><td style=";">Red</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">6</td><td style="text-align: center;;">F1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">100</td><td style="text-align: center;;">100</td><td style=";">Green</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">7</td><td style="text-align: center;;">G1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">90</td><td style="text-align: center;;">100</td><td style=";">Green</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">8</td><td style="text-align: center;;">H1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">9</td><td style="text-align: center;;">I1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">80</td><td style="text-align: center;;">100</td><td style=";">Amber</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">10</td><td style="text-align: center;;">J1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">79</td><td style="text-align: center;;">100</td><td style=";">Red</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 #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Budget</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G2</th><td style="text-align:left">=IF(<font color="Blue">E2="","",LOOKUP(<font color="Red">ABS(<font color="Green">E2-F2</font>)/F2,{0,"Green";0.101,"Amber";0.201,"Red"}</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz
 
Upvote 0
HI
The data on which the formula is running is placed in columns AK and AL.
The data begins at row 3.

I made the necessary changes in the formula in the macro .
The result is correct but the staus column gets to column AH which should not happen.
The status column should go to column AM .
The word status should be placed at cell AM2 and below that all the status.

How do I make these changes?
Thanks a lot.
 
Upvote 0
divyaquamara said:
HI
The data on which the formula is running is placed in columns AK and AL.
The data begins at row 3.

I made the necessary changes in the formula in the macro .
The result is correct but the staus column gets to column AH which should not happen.
The status column should go to column AM .
The word status should be placed at cell AM2 and below that all the status.

How do I make these changes?
Thanks a lot.
Click to expand...

Try this:

Code: 
Sub CreateStatusR1C1()
   Application.ScreenUpdating = False
    Sheets("Budget").Select
    
    LastCol = Cells(2, Columns.Count).End(xlToLeft).Column
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    Cells(2, LastCol + 1).Value = "Status"
    myFormula = "=IF(RC[-2]="""","""",LOOKUP(ABS(RC[-2]-RC[-1])/RC[-1],{0,""Green"";0.101,""Amber"";0.201,""Red""}))"
    Range(Cells(3, LastCol + 1), Cells(LastRow, LastCol + 1)).FormulaR1C1 = myFormula
    
    Cells(1, LastCol + 1).EntireColumn.AutoFit
    
   Application.ScreenUpdating = True
End Sub

Result
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>AK</th><th>AL</th><th>AM</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</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;">2</td><td style="font-weight: bold;text-align: center;;">Sr.no</td><td style="font-weight: bold;text-align: center;;">Project No.</td><td style="font-weight: bold;text-align: center;;">Lead </td><td style="font-weight: bold;text-align: center;;">Asst Lead</td><td style="font-weight: bold;text-align: center;;">YTD Budget</td><td style="font-weight: bold;text-align: center;;">YTD Actuals</td><td style=";">Status</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">1</td><td style="text-align: center;;">A1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">3.6</td><td style="text-align: center;;">4</td><td style=";">Green</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">2</td><td style="text-align: center;;">B1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">3</td><td style="text-align: center;;">C1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">6</td><td style="text-align: center;;">7</td><td style=";">Amber</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">4</td><td style="text-align: center;;">D1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">5</td><td style="text-align: center;;">E1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">7</td><td style="text-align: center;;">9</td><td style=";">Red</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">6</td><td style="text-align: center;;">F1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">100</td><td style="text-align: center;;">100</td><td style=";">Green</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">7</td><td style="text-align: center;;">G1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">90</td><td style="text-align: center;;">100</td><td style=";">Green</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">8</td><td style="text-align: center;;">H1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">9</td><td style="text-align: center;;">I1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">80</td><td style="text-align: center;;">100</td><td style=";">Amber</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">10</td><td style="text-align: center;;">J1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">79</td><td style="text-align: center;;">100</td><td style=";">Red</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 #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Budget</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">AM3</th><td style="text-align:left">=IF(<font color="Blue">AK3="","",LOOKUP(<font color="Red">ABS(<font color="Green">AK3-AL3</font>)/AL3,{0,"Green";0.101,"Amber";0.201,"Red"}</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

If the result is still wrong, then give the layout of the sheet.

Markmzz
 
Last edited:
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
622
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,587
Messages
6,179,734
Members
452,939
Latest member
WCrawford

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