Conditional Formatting wth drop down menu - need to keep the formatting consistent based on one drop down choice

[lmaltun]

I have Excel 2010 and I have looked high and low for an answer to this question . . .

I have a drop down menu that changes the values in a corresponding table. The possible drop down selctions are:

<TABLE style="WIDTH: 128pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=171><COLGROUP><COL style="WIDTH: 128pt; mso-width-source: userset; mso-width-alt: 6253" width=171><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 128pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl73 height=17 width=171>AGP_AMT</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl73 height=17>COST_OF_GOODS_SOLD</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl73 height=17>GROSS_SALES</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl73 height=17>LOST_SALES_AMT</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl73 height=17>NET_SALES</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl73 height=17>TOTAL_ALLOWANCES</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl73 height=17>TOTAL_MARKDOWNS</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl73 height=17>UNITS_SOLD</TD></TR></TBODY></TABLE>

I want cells in the table to be highlighted green when the total_markdowns numbers are < -5000. I was able to easily do this with conditional formatting.

My problem is this - I want those same cells to stay highlighted green no matter what the drop down selection is. So, if I were to select Units_Sold from the dropdown, I still want the cells that were highlighted green in the Total_Markdowns table to remain green as they indicate a markdown week.

Totally lost on how to do this and I'm not sure even where to start. Please help!!!! : )

 

[Mohammad Basem]

Welcome to the forum!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
In this case, you need to look at the value of total_markdowns and set the colour as appropriate rather than looking at the dropdown list.<o></o>

 

[lmaltun]

Thanks for getting back so quickly!

I went ahead and manually highlighted for this month but I realized after reading your reply that I forgot to mention that this is something updated monthly. The highlighted boxes will shift according to our promotional calendar which is why I was hoping for an automated method to solving the problem.

Still hoping this is an option

 

[Mohammad Basem]

Could you please post sample data and explain the data shift clearly?