Highlight cells based on a condition

[unigee]

Hi,

I would like to highlight a cell depending on which condition to use in a cells formula.

The formula calculates a forecast per month for each part number, so there are a fair few formulas (in the region of 16,000). Each cell has quite a complicated formula, but it basically looks like (in pseudo code)

=IF( quantity_between_date( month1, month2 ) = 0, calc_average_4_weeks * number_weeks_in_month, quantity_between_date(month1, month2) )

This looks at a customer schedule and calculates how many parts they need between two dates.
If the value is zero, it takes the average number of parts in a 4 week period, and multiplies it by the number of weeks in a month.
Otherwise, if simple uses the number of parts between the two dates.

This is great and works fine, but I would like to highlight a cell if I am calculating the forecast myself

I would like to be able to do something like

=IF( 
quantity_between_date( month1, month2 ) = 0,
calc_average_4_weeks * number_weeks_in_month       <-- highlight cell now,
quantity_between_date( month1, month2 )
)

I have thought of using conditioning formatting, but not sure how to do this.

Thanks

P.S
For those who are interested my formula is

=IF(Menu!$C$36=TRUE,IF(SUMIFS(INDIRECT("Calender!A"&MATCH($B31,CalenderPartList,0)&":NI"&MATCH($B31,CalenderPartList,0)),CalenderDateList,">="&DATE(YEAR(AT$3),MONTH(AT$3),1),CalenderDateList,"<"&DATE(YEAR(AU$3),MONTH(AU$3),1))=0,
AVERAGE($AJ31:$AM31)*AT$2,
SUMIFS(INDIRECT("Calender!A"&MATCH($B31,CalenderPartList,0)&":NI"&MATCH($B31,CalenderPartList,0)),CalenderDateList,">="&DATE(YEAR(AT$3),MONTH(AT$3),1),CalenderDateList,"<"&DATE(YEAR(AU$3),MONTH(AU$3),1))),0)

 

[truskawa]

Hi,

you are right to use contitional formatting for this. Not sure if you used it before so sorry if this is too detailed for you.

You need to select cells that you want to be highlighted if contition is met go to contitional formatting and select from drop down "formula is":

What to enter there.

I can try to explain on simple exaple:

assume my contition is more simple:
=IF(A1+B1=10,"ok",A1+B1)
and I want to highlight if "ok"

I would apply contitional formatting as follows:
Formula is: =$A1+$B1=10 then if this is true so "ok" cell will highlight.

So simply paste the condition used in if.

Hope this helps

 

[unigee]

Thanks for the reply, but I need to use the condition on several thousand cells.

For example
If the conditional formatting formula is '=A1+B1=10'
The next cell needs to be '=B1+C1=10'
then the next cell needs to be '=C1+D1=10' and so on and so on

Then on the next row, I need to use another conditional formatting
'=A2+B2=10', and the next cell would be '=B2+C3=10' and so on and so on

Unlike the formulas in the cells, I cant seem to "Drag" down the formula when its in a conditional format.

 

[truskawa]

Hi,

I understand you want to apply this to more than one cell, this is why I wrote you need to select all cells you want to format before writing formula.
The example I wrote works for whole column. Excel "drags" it by default.
Only if you have some fixed cells or fixed rows you want to use in your formula you need to play a little with $ singns.
Try this on my simple example and you will see it works.

 

[Marcelo Branco]

Hi,

I think truskawa's solution is ok.

Unlike the formulas in the cells, I cant seem to "Drag" down the formula when its in a conditional format.

You can use the Format Painter (yellow brush) to copy the CF to other cells.

HTH

M.

 

[unigee]

Thank you both, I understand now and it works great.

Thanks!