Conditionally change number format based on another cell's value

**Fidd$** · 12-01-2009, 01:14 PM

Hi Everyone,

I have tried to get this sorted by a few methods but nothing seems to work.
What I want the formatting to do is this:

Format the number in Column AL to '[hh]:mm' when Column C's value is ="P/T", otherwise format to 'General'

Column C's value is referenced from another sheet in the same workbook via a VLOOKUP function.

Column AL's value is based on an IF formula which goes like this:
=IF($C4="F/T",SUM($AJ4)+($AK4/2),SUM($D4:$AH4))

Basically the above formula asks if Column C's value is F/T then count (because if F/T then the corresponding values in that row are whole numbers). If not, then SUM (because if P/T the corresponding values in that row are set to [hh]:mm format).

I have another sheet in the same workbook which has code (quoted below) which does something similar but I don't understand it enough to get it doing what I want for the sheet in question.

Please Login or Register  to view this content.

I know the above may sound quite confusing (it does to me!!) so if further explanations and/or example sheet is needed then please ask.

Many thanks,
Tony

**DonkeyOte** · 12-01-2009, 01:59 PM

A formula recalculating will not invoke the Change event - you would need to use the Calculate event which unfortunately unlike Change does not have "Target"
ie you will need to iterate each cell in the C range and adjust format as appropriate based on the iterated value

This Calculate method obviously brings with it additional processing overheads as you're invoking the routine more often than is strictly necessary - ie lots of actions can trigger a calculation
(no cells in C may be recalculating at run time but you can't really know that for sure)

**DonkeyOte** · 12-01-2009, 02:09 PM

Continuing on from the above post... perhaps then something along the lines of the below will get you started ?

Please Login or Register  to view this content.

Adjust ranges etc to suit.

**Fidd$** · 12-01-2009, 02:09 PM

Thanks for the reply DonkeyOte.
I think it was yourself who provided the code I quoted from another thread I had posted a wee while back.

I had thought about using Conditional format with an IF statement but that may in itself cause unneccessary bulking up of the file.

What if the reference cell was other than C? For instance, Columns D:AH either has '[hh]:mm' or 'general' format numbers which are populated via a validation drop down. Would this be adequate to invoke the 'change'?

**DonkeyOte** · 12-01-2009, 02:11 PM

Originally Posted by Fidd$

What if the reference cell was other than C? For instance, Columns D:AH either has '[hh]:mm' or 'general' format numbers which are populated via a validation drop down. Would this be adequate to invoke the 'change'?

Can you elaborate - I don't quite follow.... if C is the underlying trigger and is independent of D:AH then I don't think you could use those cells to determine format of AL, no.

How many cells are we talking about ?

**Fidd$** · 12-01-2009, 02:17 PM

Yep, apologies. I think I'm a bit tired and need sleep since you've pointed out something so nab-inducingly obvious!
Wasn't thinking straight there...........

I maybe need to take a break from this and come back to it later.

I will have a gander at what you've suggested above Donkey and get back to you with feedback.

Thanks again,
TOny

**Fidd$** · 12-03-2009, 07:58 AM

Hi DonkeyOte,

Just a wee bit o feedback as promised.

It works amazingly well, I don't think it would be that intensive as per your explanation on the worksheet_calc event as there are only around 150-200 rows. Tried it with more sheets in the same workbook and I haven't noticed any slowing down. Seeing as I need 12 of them it works great.

You Sir, are a genius.

Cannot thank you enough for your help Donkey.