Formula refresh

[antony moseley]

Hi,

If have formulas in cells that look to other cells to get an answer, but I have to manually use the F2 button as though I have made an edit to the cell and then leaving it, obviously Excel considers this a change and updates the cell and any Conditional formatting then kicks in.

Is there anyway I can write any code to do this over a large range of cells?

Thanks
Antony

[Domski]

Have you accidentally set calculation to manual?

Dom

[antony moseley]

Hi,

No cal is on automatic - I have attached an example.

Basically if I put 1,2 ,3 4,or 5 into the cells on Goods In tab, it will turn a colour, this number feeds in the LT tab, that should turn the same colour, however I then have to do the F2 bit for it to change other, otherwise it doesn’t do anything

Hopefully it is abit clearer with my issue

Thanks

Antony

[Domski]

A worksheet change event only fires when a cell is changed by the user not as the result of a formula change. If the layout is the same you can get rid of the Change event code on the LT sheet and just use this on the LT sheet:

Please Login or Register  to view this content.

Dom

[antony moseley]

Hi,

Thanks for the above - the LT tab will be made up of several others individual tabs, so the layout wont be the same

Any other ideas???

Thanks

Antony

[Domski]

I guess you could use a worksheet activate or calculate event to loop through and format the cells as you require.

Dom

[antony moseley]

HI,

As much as i would like to say - yeah, i know how to do that..... but i dont.Any pointers on how to do it?


Thanks

Antony

[Domski]

Something like this on the LT sheet's code page:

Please Login or Register  to view this content.

Dom

[antony moseley]

HI,

Unless i have read wrong im not sure this works for me - i have attached a version of many tabs to one

Thanks

Antony

[antony moseley]

take it back - the above did work

Thanks

Antony