Marge Cells based on Cell value

[anjara99]

Hello,

I have varies list of equipment, I have done background calculation to determine hourly, daily, weekly, or monthly rate will apply to this equipment. I have "D" for daily, "H" for hourly, "W" for weekly, and "M" for monthly.
Based on cell value "W" or "M" I want to merge Week row for weekly rate or all weeks row for a monthly rate. Can you please provide a solution?

I have these cell values in different sheet in the same excel file.

Thank you in Advance.

 

[BobUmlas]

Right click the sheet tab, select View code, enter this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Select Case UCase(Target.Value)
Case "M"
If Target.Column = 2 Then
Target.ClearContents
Target.Resize(1, 28).Merge
End If
Case "W"
If Cells(3, Target.Column) = "Monday" Then
Target.ClearContents
Target.Resize(1, 7).Merge
End If
End Select
End Sub

 

[anjara99]

Flour Tracking Sheet-Rev01 - Copy.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1		Month 1
2		Week 1						Week 2						Week 3						Week 4
3		Monday	Tuesday	Wednesday	Thrusday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thrusday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thrusday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thrusday	Saturday	Sunday
4	Labour
5	Project Manager
6	Superintendent
7	Foreman
8	Lead Hand
9	Operator/ Driver
10	Senior Operator
11	Trackman
12	Equipment
13	F150 Pickup
14	Crewcab Truck
15	Fuel Truck
16	Hi-rail Crewcab Truck
17	Thermite Welding Truck
18	Loader
19	Tamper, Switch Chase
20	Tamper, Mark IV
21	Ballast Regulator
22	Production Tie Inserter
23	Spiker CX
24	Spiker Dual Puller
25	Hi-rail Excavator 20k lb to 40k lb
26	Hi-rail Excavator 40k lb to 50k lb
27	Hi-rail Loader
28	Hi-rail Grapple Boom Truck
29	Hi-rail HIAB Boom Truck
30	Hi-rail Speedswing
31	Hi-rail Dump Track
32	Highway Tractor /cw Double Drop Trailer
33	Tie- Attachment
34	Chain Saw / Skill Saw
35	Hydraulic Power Pack
36	Hydraulic Spiker
37	Hydraulic Spike Puller
38	Hydraulic Impact
39	Hydraulic Tamper
40	Hydraulic Rail Drill
41	Gas Stockrail Grinder
42	Gas Rail Saw
43	Gas Tie Drill
44	Gas Profile Grinder
45	Air Compressor 185 CFM
46	Air Impact Wrench
47	Air Spikers
48	Air Pavement Breaker
49	Back Pack Air Blower
50	Electric Generator
51	Electric 9" Grinder
52	Electric Core Drill
53	Push Cart
54	Thermite Welding Equipment
55	Mobile Welder
56	Weld Wire Feeder
57	Miscellaneous Track Tools
Cost

 

[anjara99]

Can you please explain how would that work, It didn't work for Me

 

[BobUmlas]

You have to enter a M in the first Monday column or a W in any Monday column.

 

[anjara99]

When i copy and paste your program in vba it does not work. am i doing anything wrong

 

[BobUmlas]

You must copy the code into a Sheet module, not a regular module. Right-click the sheet tab, select View Code, then paste my code there.

 

[anjara99]

It is working, now I have to type "W" or "M" every time, can I relate this to a specific cell from a different sheet that automatically shows "M" or "W"

 

[BobUmlas]

Sorry, I don't understand...

 

[anjara99]

I have worksheet "Chargeable_Rate" where it shows weekly pop-up as "W" or"W+H" in column I, Column Q, Colum Y, Column AG, Column AO. if it is "w" or "w+h" I want to merge that specific week.
In column AP I have a monthly pop-up as"M" or "M+H" so I want to merge that entire month cells in worksheet "Cost".
Can you please help me with this?