specific value returns to colour cells

[Aberdham]

Hi Guys,

I Need some help with regard to when certain cells have value and return to colour-shaped Status:

I have 2 different workbooks:

one indicating:

[TABLE="width: 437"]
<tbody>[TR]
[TD]Jan
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]
[TABLE="width: 87"]
<tbody>[TR]
[TD]Serial number
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Process fee
[/TD]
[TD]Registration fee
[/TD]
[TD]process cost
[/TD]
[TD]Registration cost
[/TD]
[TD]accrual process cost
[/TD]
[TD]accrual Registration cost
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 87"]
<tbody>[TR]
[TD]136552
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]56554
[/TD]
[TD]58412
[/TD]
[TD]45258
[/TD]
[TD]45245
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 87"]
<tbody>[TR]
[TD]168953
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]59596
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]58555
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 87"]
<tbody>[TR]
[TD]123658
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]88859
[/TD]
[TD][/TD]
[TD]59658
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 87"]
<tbody>[TR]
[TD]562563
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]55569
[/TD]
[TD]57854
[/TD]
[TD][/TD]
[TD]35698
[/TD]
[TD]45858
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 87"]
<tbody>[TR]
[TD]246558
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]85895
[/TD]
[TD]45895
[/TD]
[TD]59598
[/TD]
[TD][/TD]
[TD][/TD]
[TD]36562
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 87"]
<tbody>[TR]
[TD]546454
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 87"]
<tbody>[TR]
[TD]456132
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]56555
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 87"]
<tbody>[TR]
[TD]545651C
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]56985
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]589858
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]698959
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]595895
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 87"]
<tbody>[TR]
[TD]
and the other one indicating:

Description:
- all cells process and Registration fees; process cost (or accrual process cost) Registration cost (or accrual) have value Returns to on Progress.
- all cells have no values Returns to (a green Label on 04)
- process and Registration cost(including accrual process and Registration) have value; no values in process or Registration fee ( a red Label on O5)
- values in process fee and process cost or accrual process cost (a yellow Label on 06)
- when Serial number =589858, 698959 etc Returns to an orange Label on 07

[TABLE="width: 58"]
<tbody>[TR]
[TD="class: xl66, width: 77, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 77"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan
[/TD]
[TD]Feb
[/TD]
[TD]March
[/TD]
[TD]April
[/TD]
[/TR]
[TR]
[TD]Serial number
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]136552
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]168953
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123658
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]562563
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]246558
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]546454
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456132
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]545651C
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]589858
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]698959
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]595895
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
could any of you come up with a formula or a VBA code to help me do the trick?

any help would be greatly appreciated !

Thanks

 

[alansidman]

I dont understand what you mean by "returns an orange label on 07," etc. What are your referring to? What is an 04 or 05, etc. Perhaps you could show an example of what you mean in your illustration for each of your issues.

 

[Aberdham]

I dont understand what you mean by "returns an orange label on 07," etc. What are your referring to? What is an 04 or 05, etc. Perhaps you could show an example of what you mean in your illustration for each of your issues.

Hi Alan,

Thanks for your quick Response !
O4;O5;O7 refers to the column and row.
I Need to have the respective value filled in the workbook indicating the Status; different Status versus different colour
with exception of the first description Returns to text as "on Progress"

hope to hear from you soon
Thank you

 

[Aberdham]

I hope that's a bit more clear now guys...

I did try to Format it as a table but it doesn't seem to work.

please help me out...

thanks...

 

[Aberdham]

would multiple if and lookup works in this case?

 

[Aberdham]

any idea anyone?

 

[alansidman]

I still cannot correlate your example shown to your explanation. Perhaps a sample showing what you are doing with a before and after example.

 

[Aberdham]

[TABLE="width: 1907"]
<tbody>[TR]
[TD]Serial number[/TD]
[TD]Process fee[/TD]
[TD]Registration fee[/TD]
[TD]process cost[/TD]
[TD]Registration cost[/TD]
[TD]accrual process cost[/TD]
[TD]accrual Registration cost[/TD]
[TD][/TD]
[TD][/TD]
[TD]Column1[/TD]
[/TR]
[TR]
[TD]136552[/TD]
[TD]56554[/TD]
[TD]58412[/TD]
[TD]45258[/TD]
[TD]45245[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]Description:[/TD]
[/TR]
[TR]
[TD]168953[/TD]
[TD]59596[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]58555[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]- all cells process and Registration fees; process cost (or accrual process cost) Registration cost (or accrual) have value Returns to on Progress.[/TD]
[/TR]
[TR]
[TD]123658[/TD]
[TD]88859[/TD]
[TD] [/TD]
[TD]59658[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]- all cells have no values Returns to (a green Label on 04)[/TD]
[/TR]
[TR]
[TD]562563[/TD]
[TD]55569[/TD]
[TD]57854[/TD]
[TD] [/TD]
[TD]35698[/TD]
[TD]45858[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]- process and Registration cost(including accrual process and Registration) have value; no values in process or Registration fee ( a red Label on O5)[/TD]
[/TR]
[TR]
[TD]246558[/TD]
[TD]85895[/TD]
[TD]45895[/TD]
[TD]59598[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]36562[/TD]
[TD][/TD]
[TD] [/TD]
[TD]- values in process fee and process cost or accrual process cost (a yellow Label on 06)[/TD]
[/TR]
[TR]
[TD]546454[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD]- when Serial number =589858, 698959 etc Returns to an orange Label on 07[/TD]
[/TR]
[TR]
[TD]456132[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]56555[/TD]
[TD]45456[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]545651C[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]56985[/TD]
[TD]56985[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]589858[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]698959[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]595895[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Column1[/TD]
[TD]Column2[/TD]
[TD]Column3[/TD]
[TD]Column4[/TD]
[TD]Column5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]March[/TD]
[TD]April[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Serial number[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]136552[/TD]
[TD]on progress [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]168953[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123658[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]562563[/TD]
[TD]on progress [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]246558[/TD]
[TD]on progress [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]546454[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456132[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]545651C[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]589858[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]698959[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]595895[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]




Perhaps this might help... But i can't seem to get the cells filled with Color here[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col span="2"><col></colgroup>[/TABLE]